Does Partition Creation Order Matter?

Question

I'm in a situation where I have taken over a very large db that is primarily a single table that is partitioned. Since it hasn't been partitioned for a while, I need to re-start the partitioning, but it is a 24/7 business and it can't be out for a few hours while SPLITing the older partitions.

If I was to create the new partition for the end of this month, then go back and SPLIT the previous partitions - is there a problem with doing them out of order, other than the partition id being in the wrong order?

Is there any performance or maintenance differences if the order of the partition id is not in sequence with the partition key order?

Answer 1

...if the order of the partition id is not in sequence with the partition key order

SQL Server always maintains partition ids in partition boundary order. No matter how you split or merge partitions, the order of boundary values always matches the order of partition ids. SQL Server renumbers as necessary.

Concerning performance of the split operation itself, you will want to organize things so that the minimum (ideally, none at all) physical data movement occurs. This typically involves careful thought and testing in advance, using SWITCH and CREATE INDEX…WITH DROP_EXISTING = ON.

Review the following all by Dan Guzman:

• Table Partitioning Best Practices
• Remediating a Table Partitioning Mess
• Move a Partition to a Different File Group Efficiently

If you need help designing the process, ask a new question with DDL for the table, indexes, partitioning scheme, function, and file groups. Also include a clear representation of the start and end states i.e. which data should go where.