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?
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