The Best Solution for SQL Server Partitioning Sliding Window

You already know what is Data Partitioning in SQL Server. It's nothing new. But turns out that there is a shortage of solutions available online for easily managing partitioned tables.

Sure, there are a bunch of blog posts and guides and webinars about WHAT is partitioning in SQL Server, and WHAT is a "Sliding Window"... And sure, there is even enough information out there to help you construct your own partition sliding window maintenance for your specific partition functions and schemas and tables.

But if you know me, you should already know that my idea of a "best solution"... is a GENERIC solution. One that could be easily reused for multiple different use cases. One that requires minimum customizations and coding to work for as many different scenarios as possible.

That is why I created The Best Solution for implementing SQL Server Partitioning Sliding Window. Namely - a GENERIC one.

(yeah I know it's a rather pretentious and self-serving title... But I had to catch your attention somehow, didn't I?)

Before we begin, there are a few "ground rules" we should understand first:

1 - Partition Functions define the partition ranges

This means that whenever we want to eliminate an old partition range or add a new partition range, the PARTITION FUNCTION is the object that we actually need to modify.

Each partition function could have different ranges, affecting different tables and indexes.

So, when we set up some kind of a "maintenance" job to delete old partitions and/or create new partitions, decide how many ranges we want to delete, and how many new ranges we want to create, we would do so per each partition function.

In other words: The partition function must be one of our parameters.

2 - Considerations when creating new partition ranges

When creating new partition ranges, there are a few assumptions we must consider:

  1. We always split the last partition range, which is the one with the maximum value.

  2. The last partition range must always be empty so that no data movement would be required during the split.

  3. To make sure that the last partition range is empty, we should create several "buffer" partition ranges in advance.

  4. The partition range intervals should be uniform. This isn't a must, but if they're uniform then it would make partition management much easier.

3 - Considerations when eliminating old partition ranges

When purging old partition ranges, there are a few assumptions we must consider:

  1. We always eliminate the first partition range, which is the one with the minimum value.

  2. The first partition range must be empty so that no data movement would be required during the merge.

  3. To make sure that the first partition is empty, we truncate the data inside of it before performing the merge. IMPORTANT: The solution supports only SQL Server version 2016 (13.x) and newer, which has the TRUNCATE TABLE ... WITH PARTITION syntax. A backward-compatible version may be implemented in the future.

The Stored Procedures

Without further ado, following below are the stored procedures included in this solution.

There are only two stored procedures needed: