0

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!

2

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.

| improve this answer | |
  • 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? – Avi Feb 6 at 12:51
  • 1
    @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. – Dan Guzman Feb 6 at 13:32
  • 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. – Avi Feb 6 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.