I have long been curious about data movements when splitting or merging a partitioned table. For this test I have twp test tables with about 5 million rows each. First table will be partitioned using RIGHT and second table will be partitioned using LEFT.
The distribution of data look like this and I choose two dates at the ends to make the timings more visible.
I choose 2016-06-01 and 2022-01-01 as the partition dates and then the partitions are each populated with different number of rows.
What about SPLIT range?
For the 1st test I used the partition function with right and 2022-01-01. It took 11 seconds and used 911k reads.
For the 2nd test I used the partition function with right and 2016-06-01. It took 451 seconds and used 7583k reads.
It seems when the partitioning function uses right, all data to the right of the split date is moved to a new partition.
For the 3rd test I used partition function left and 2022-01-01. It took 268 seconds and used 7461k reads.
For the 4th test I used partition function left and 2016-06-01. It took 20 seconds and used 531k reads. Again, it seems the time it takes to create the new partition is determined by the number of rows that will be stored in the new partition.
What about MERGE range?
For the 5th test I used the partition function with right and 2022-01-01. It took 1 second and used 22k reads.
For the 6th test I used the partition function with right and 2016-06-01. It took 11 seconds and used 1044k reads.
For the 7th test I used the partition function with left and 2022-01-01. It took 20 seconds and used 531k reads.
For the 8th test I used the partition function with left and 2016-06-01. It took 1 second and used 629k reads.