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.