Sliding Window-Switch Partition-Split Range-Merge Range

Before you read this article, you need to know what the concept of partition is and how can we create partition for  a table.

You may want to read the article titled “How To Create Partition On SQL Server” for detailed information on these topics.

The “Sliding Windows” scenario is to ensure that the same number of partitions are always present in the partitioned table.

For example, you may only want to keep the last 1 year’s data in a partitioned table.

And at the end of each month or at the beginning (If your partition is monthly) you create a new partition and transfer the oldest partition to another table by switching.

This way you can only keep the last year’s data on the partitioned table.

We will call the partitioned table as original table in the following parts of the article.

To do our example, you need to apply the example in the article “How To Create Partition On SQL Server“.

In our example, the data before 2007 is on a partition, the data between 2007 and 2008 on another partition, 2008 and later on another partition.

We only want the last 3 partitions.

That is, when the data for 2009 is added, the first partition (2007) needs to be archived.

So we will switch the first partition to another table(archieve table).

This switch operation is a very short process.

Because the archive table and the original table must be in the same file group.

We will create the archive table with the following script.

Of course it should be the same structure as the original table (we dont add identity because these values will come from the original table)

We are creating the archive table on filegroup1. (We create partitions and file groups in previous article)

Because the first partition we want to switch to is kept on Filegroup1.

Before switching the first partition we will create a new filegroup with the help of the following script and create one file on this filegroup for new partition.

SWITCH PARTITION

With the help of below script;

• We switch the partition that contains the data from 2007 and earlier to the PartitionArchiveExample table.
• We are updating the Partition function so that it will not contain any info for 2007 and before.
• We specify the filegroup to be used for the next partition(Filegroup4 for 2009 data) for partition scheme.
• We are updating the Partition function to use the new filegroup for data 2009 and after.

TRANSFER ARCHIVED PARTITIONS TO ANOTHER DATABASE

To prevent the database from growing too large, I recommend that you move the archieve table to another database with the following script after this operation.

You can also create a stored procedure and automate the operations we have done above.

You should test all these operations before applying production.