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.