Partition boundary value, ID and partition number

 

SQL server 2005 uses partition function to manage ranges of partitions. However, the boundary ID and partition number of each partition is not fixed, i.e. they will change for existing partitions if spliting/merging a partition. SQL server always number the partition ID and boundary ID for boundary values in the ascending order.

CREATEPARTITIONFUNCTION [DataDateRange]([datetime])ASRANGERIGHTFORVALUES(cast(N’05/01/2004 00:00:00′asdatetime),cast(N’06/01/2004 00:00:00′asdatetime),cast(N’07/01/2004 00:00:00′asdatetime))

GO

— Check the partition function definition:

SELECT

*FROMsys.partition_functions

GO

— To get information about individual partition functions parameter

SELECT*FROMsys.partition_parameters

GO

— Check the partition function range values

SELECT

*FROMsys.partition_range_values

GO

function_id boundary_id parameter_id value

———– ———– ———— ————————

65536 1 1 2004-05-01 00:00:00.000

65536 2 1 2004-06-01 00:00:00.000

65536 3 1 2004-07-01 00:00:00.000

We can see three boundaries defined. For 2004-05-01, the boundary ID is 1.

If we  Check the partition number for any date time value, say 2004-06-03,

SELECT$PARTITION.[DataDateRange](‘2004-06-03’)AS PartitionNumber

We get the partition number is 3

GO

Now modify the partition funciton by spliting a range

ALTER

PARTITIONFUNCTION [DataDateRange]()

SPLIT

RANGE(cast(N’04/01/2004 00:00:00′asdatetime))

GO

SELECT*FROMsys.partition_range_values

GO

function_id boundary_id parameter_id value

———– ———– ———— ————————

65536 1 1 2004-04-01 00:00:00.000

65536 2 1 2004-05-01 00:00:00.000

65536 3 1 2004-06-01 00:00:00.000

65536 4 1 2004-07-01 00:00:00.000

We can see that the partition boundaries are chanegd and all existing boundaries now have a new ID, e.g. the boundary ID for 2004-05-01 is 2.

If we check the partition number for 2004-06-03 again, we will get partition number 4.

SELECT

$PARTITION.[DataDateRange](‘2004-06-03’)AS PartitionNumber

Since SQL server regenerates partition boundary ID sequencially based on boundary value every time when splitting or merging boundaries, we should NOT build any static relation between user data with boundary ID or partition number. We need to use $PARTITION function all the time to get the current value.

 Edit: 2012-09-05, fixed the typo. Many thanks to Dave. 

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

3 Responses to Partition boundary value, ID and partition number

  1. David W says:

    Error in last paragraph : Where you said “We should build any static relationship”, I think you meant “we should NOT build any static relationship”.

  2. Kiran says:

    Nice Explanation… 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s