Table Partition Function Empty Partitions

Question

I plan to partition my table monthly along a date column, and have read at few places, that it is recommended (in fact a best practice) to keep empty partitions at both "end" of the range. https://techcommunity.microsoft.com/t5/premier-field-engineering/oops-i-forgot-to-leave-an-empty-sql-table-partition-how-can-i/ba-p/370563

As a naive approach I would create my partition function like this:

CREATE PARTITION FUNCTION MyPf(DATE)
AS RANGE RIGHT FOR VALUES (
'1900-01-01',
'2019-10-01','2019-11-01',...,...,...,'2022-08-01',
'9999-12-31');
CREATE PARTITION SCHEME MyPs AS PARTITION MyPf
ALL TO (MySingleFileGroup)

I can guarantee that no data older than 2019年10月01日 will be inserted to the table, and I plan to keep SPLIT partitions once I reached 2022年08月01日, and keep doing so up until 9999年12月31日. I also plan to regularly TRUNCATE old partitions and MERGE the old partitions range.

Did I miss anything obvious regarding the best practice with this setup? My only goal is to be able to split and merge without moving data around.

Thank you!

Answer 1

I can guarantee that no data older than 2019年10月01日 will be inserted to the table, and I plan to keep SPLIT partitions when I am reaching 2022年08月01日

As long as no existing data qualifies for the new partition boundary range, no data movement is required for the SPLIT.

I also plan to regularly TRUNCATE old partitions and MERGE the old partitions range.

Similarly, the merged partition will be empty due to the preceding TRUNCATE so no rows will need be moved to accommodate the new boundaries.

• Avi: Thanks @Dan, so it seems I am on the good track to keep split and merge my monthly data without any data movement on the same filegroup, right?
• Dan Guzman: @Avi, yes. And kudos for the same filegroup as I often see folks create filegroups for each partition mindlessly. This adds unneeded complexity without value unless there's a specific reason to do so.
• Avi: Thanks again. Yes, for this setup, I don't want filegroup backups, neither want to put old files to slow disks, as I am on the same SAN all the time.