Implemented my first "sliding window" partitioned table. Wound up being easier than I expected, especially since the table will only have 2 partitions for now - Current and Archive.
Most sliding window schemes I've seen out there on the intertubes make use of a date as the partition key and update the sliding window based on the current date. In this case, the data comes in somewhat sporadically, so although it's date-based (a month key), I made the partitioning data-driven instead of date-driven. So for starters we have a RANGE LEFT scheme like this:
Partition Month Key
Current -
Archive 88
Say we're currently on month 90. So months 89 and 90 are in Current, and 1-88 are in Archive. When the sliding window proc runs, it does essentially this:
SELECT @NewBoundary = MAX(monthKey)-2 FROM tbPartitionedFoo
SELECT @CurBoundary = CAST(prv.value as smallint) FROM sys.partition_functions AS pf
JOIN sys.partition_range_values as prv
ON prv.function_id = pf.function_id
WHERE pf.name = 'partfn_foo'
(If there was more than one partition range value, a MIN() or something would be needed to grab the appropriate value.)
IF @NewBoundary > @CurBoundary
BEGIN
ALTER PARTITION FUNCTION partfn_foo() SPLIT RANGE (@NewBoundary)
ALTER PARTITION FUNCTION partfn_foo() MERGE RANGE (@CurBoundary)
END
So when we start getting monthKey 91 data and the maintenance proc runs, it will do this:
IF 89 > 88
BEGIN
ALTER PARTITION FUNCTION partfn_foo() SPLIT RANGE (89)
Now we have 3 partitions:
Partition Month Key
Current -
temp 89
Archive 88
ALTER PARTITION FUNCTION partfn_foo() MERGE RANGE (88)
END
And now we're back to 2:
Partition Month Key
Current -
Archive 89
