There are several tables used to track and control partitioned tables in SQL Server. Two of these seem to have columns with the same meaning and values but with different names.
sys.partitions.partition_number has a column partition_number.
sys.partition_range_values has a column boundary_id
These seem to be the same values, for a given partition scheme. FWIW, partition number is also returned by the special function $PARTITION as in
$PARTITION.<partition function>(value)
This also seems to correspond to the boundary_id column in the row in the sys.partition_range_values table containing the matching value.
FWIW from Microsoft articles:
boundary_id int ID (1-based ordinal) of the boundary value tuple, with left-most boundary starting at an ID of 1.
partition_number int Is a 1-based partition number within the owning index or heap. For non-partitioned tables and indexes, the value of this column is 1.
Can anyone confirm that these the two columns of these tables do indeed hold the same values for a given partitioning function and scheme?
I would like an official reference that my search skills seem unable to find.
No, it does not.
sys.partition_range_values is foreign-keyed to sys.partition_functions over the function_id column, which is unique in the DB.
You can connect this with the table via sys.partition_schemes, which has a function_id column. This also has a data_space_id column, which has a many-to-many mapping to sys.data_spaces via sys.destination_data_spaces.
And as far as I can tell, destination_data_spaces.destination_id is the same as sys.partitions.partition_number.
Complicated, but if you have used partition functions and schemes you will see how it maps out to the way they are used.