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)
|
1 2 3 4 5 |
CREATE TABLE [dbo].[PartitionArchiveExample]( [ID] [int] NOT NULL, [Name [varchar](250) NULL, [RecordTime [datetime] NULL ) ON FileGroup1 |
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.
|
1 2 3 4 5 |
ALTER DATABASE Test ADD FILEGROUP FileGroup4 ALTER DATABASE Test ADD FILE ( NAME = DataFile4, FILENAME = 'C:\MSSQL\TestDataFile4.ndf') |
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.
|
1 2 3 4 5 6 7 8 9 10 |
ALTER TABLE dbo.PartitionOrnek SWITCH PARTITION 1 TO dbo.PartitionArchiveExample ALTER PARTITION FUNCTION [RecordTimePartition]() MERGE RANGE ('2007-01-01') ALTER PARTITION SCHEME [RecordTimeScheme ] NEXT USED [FileGroup4] GO ALTER PARTITION FUNCTION [RecordTimePartition]() SPLIT RANGE ('2009-01-01') |
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.
|
1 2 3 4 5 |
BEGIN TRANSACTION INSERT INTO TestArchieveDB.dbo.PartitionArchiveExample SELECT * FROM Test.dbo.PartitionArchiveExample TRUNCATE TABLE Test.dbo.PartitionArchiveExample COMMIT TRANSACTION |
You can also create a stored procedure and automate the operations we have done above.
You should test all these operations before applying production.
