Are Partition Boundaries IDs (in sys.partition_range_values eg.) guaranteed to be ordered by partition range?

user_1662268 0
Viewed 155

Suppose you have a Partition Function, and you are dynamically adding and removing Range Boundaries to that Function, using ALTER PARTITION SPLIT RANGE and ALTER PARTITION MERGE RANGE.

You can interrogate sys.partition_range_values to establish what the current range boundaries are.

It appears that no matter what you create, split and merge the ranges in, that sys.partition_range_values table will always show that the boundary_ids (and equivalently the partition ids, in other sys tables) are in ascending order of the boundary values.

Is that actually guaranteed?

Can I be certain that boundary_id 1 will always be the left-most boundary of the Partition Function?

database-partitioning sql-server sql
1 Answers

Can I be certain that boundary_id 1 will always be the left-most boundary of the Partition Function?

Yes. This is specifically called out in the sys.partition_range_values documentation:

ID (1-based ordinal) of the boundary value tuple, with left-most boundary starting at an ID of 1.

My interpretation of "left-most boundary starting at an ID of 1" is that boundary values are ordered. The CREATE PARTITION FUNCTION doc also mentions boundary value ordering:

If the values are not in order, the Database Engine sorts them, creates the function, and returns a warning that the values are not provided in order. The Database Engine returns an error if n includes any duplicate values

user_37111620
Related