Split Range - Partitions to Filegroups

So I asked the questions: How do I know which filegroup any given partition is mapped onto? How do I know which is the new partition when a range is split on a new boundary. And which partition keeps the data and which is removed when two partitions are merged? The first question is answered by looking at the sys.destination_data_spaces table. The second is answered in this post, and the third will be the subject of a later post.

We can set up a partition for this exercise with the following SQL:
CREATE PARTITION FUNCTION pfLeft(INT) AS RANGE LEFT FOR VALUES()
CREATE PARTITION SCHEME psLeft AS PARTITION pfLeft TO (FG1)

Our diagnostic SQL, which we run repeatedly, is:
SELECT destination_data_spaces.*
FROM sys.destination_data_spaces
INNER JOIN sys.partition_schemes ON sys.destination_data_spaces.partition_scheme_id = sys.partition_schemes.data_space_id
WHERE sys.partition_schemes.name = 'psLeft'
ORDER BY destination_id ASC

For a partition function with no boundaries (i.e. all rows fit in one partition) we see that the first (one and only) destination_id maps to the data_space_id of FG1.

partition_scheme_id destination_id data_space_id
------------------- -------------- -------------
65601               1              2

If we set the next used filegroup to FG2 and then split the partition on the boundary of 0 we see:

partition_scheme_id destination_id data_space_id
------------------- -------------- -------------
65601               1              3
65601               2              2

The interesting bit is that FG2 is now used for the first destination_id. When the existing partition was divided, the LEFT sub-partition (including value 0) was assigned to the new filegroup. Let's try it again by setting the next used filegroup to FG3 and splitting on 100:

partition_scheme_id destination_id data_space_id
------------------- -------------- -------------
65601               1              3
65601               2              4
65601               3              2

Again, it's the LEFT sub-partition (including value 100) of the partition that was being divided that is assigned to the new filegroup. If we try this one more time with a split on -100, I'd hope to see FG4 take up the LEFT sub-partition of our left-most partition.

partition_scheme_id destination_id data_space_id
------------------- -------------- -------------
65601               1              5
65601               2              3
65601               3              4
65601               4              2

Lets do everything from the start, but this time we'll use:
CREATE PARTITION FUNCTION pfRight(INT) AS RANGE RIGHT FOR VALUES()
CREATE PARTITION SCHEME psRight AS PARTITION pfRight TO (FG1)

We'll also need to change the diagnostic code to show the psRight partition scheme:
SELECT destination_data_spaces.*
FROM sys.destination_data_spaces
INNER JOIN sys.partition_schemes ON sys.destination_data_spaces.partition_scheme_id = sys.partition_schemes.data_space_id
WHERE sys.partition_schemes.name = 'psRight'
ORDER BY destination_id ASC

Then we run all the splits in order: 0 -> FG2, 100 -> FG3, -100 -> FG4

destination_id data_space_id
-------------- -------------
1              2
2              5
3              3
4              4

The latest results are consistent with what we've seen previously, but now it's the RIGHT segment (including the boundary value) of the newly divided partition that gets moved onto the new filegroup.

In summary, if your partition function is defined as RANGE LEFT, then a SPLIT RANGE (x) alteration will split an existing partition and the LEFT segment will become the new partition on the NEXT USED filegroup; the other segment stays on its original filegroup. Conversely, if your partition function is defined as RANGE RIGHT, then the RIGHT segment becomes the new partition on the NEXT USED filegroup.