Behavior of New Inserts with SQL Server Partitioning After Default Partition Usage

Question

I have a table with the column PartitionMonth as a partition key.

TABLE {
 TrackingId UNIQUEIDENTIFIER,
 PartitionMonth INT -- YYYYMM
}

We initially created partitions manually up until 2021. However, after that, we missed adding new partitions, so all data added post-2021 ended up in the default (or NULL) partition. Now, I plan to add new partitions starting from December 2024. Given that the table holds a large volume of data, when inserting new records from December 2024 onward, will SQL Server scan all existing records to place them in the correct partition, or will it simply insert the new records directly into the new partition?

Answer 1

Once you split the partition, new rows will be inserted in the correct partition without reading the other partitions. SQL Server will only look at the partition function to decide which partition the row should land in.

• Naghaveer R: With the SPLIT RANGE RIGHT partition function, adding a new partition does not involve scanning previous partitions. However, with the SPLIT RANGE LEFT partition function, adding a new partition requires scanning the previous partition (or NULL if no other partitions exist), consuming nearly 50% of the time it would take to insert the same number of entries into the previous partition.

Answer 2

After you missed adding new partitions, all data was storing at partition which start from 202112(assume you ended on Dec.) The partitions are look like this: [...], [a bound - 202112], [202112 - later](this is the default partition).

When you create a new partition start from 202412, the partitions will look like this: [...], [a bound - 202112], [202112 - 202412], [202412 - later]. That means you split [202112 - later] to 2 partitions: [202112 - 202412], [202412 - later].

All rows already exists which key value less than 202412 are still in the partition [202112 - 202412] without moved, and those greater than 202412 will be move to [202412 - later].

New rows inserted less than 202412 will be stored in partition [202112 - 202412]. greater than 202412 will be stored in [202412 - 202412 later], until you split it again.

• David Browne - Microsoft: SQL Server partitions are half-open intervals. So better to say, eg (202112 - 202412] to indicate which endpoint is contained in the partition.