Sliding Window partitioning

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