Table and Index Partitioning - Microsoft SQL Server 2012 Performance Tuning - page 325

We then merge partition 2 with partition 1 by specifying the range value of partition 2 in
the ALTER PARTITION …MERGE statement. We alter the partition scheme ps_Quaterly_
RangeRight to set the next used filegroup. As long as we have all partitions on the PRIMARY
filegroup, this is not necessary. However, if we have multiple partitions on multiple filegroups,
we have to specify the name of the filegroup to be used for the next partition. After merging
the partition, we examine the partitions again, to verify that the partition has indeed been
removed. You should now see a total of five partitions, rather than six partitions.
Next, we remove the data for oldest quarter and remove its corresponding partition as well.
Next, we need to load new bulk data and create new partitions. The process is exactly the
reverse of what we do to purge old data.
We load data for the new quarter, January 2012 to March 2012, into our staging table
tbl_MyStagingData , by generating 100,000 records. Note that we create a check
constraint on the table, which checks that the date falls between January 2012 and March
2012. This check constraint is a mandatory part, without which it is not possible to load data
from the staging table into the partition.
We then add a new partition by altering the partition function by splitting the last empty
partition (January 12 to March 12) by specifying a new range value, 20120401 . This creates
one new partition, to the "right", whose lowest range value happens to be 20120401 .
Once the partition is created, we switch tbl_MyStagingData to partition 5 of the
tbl_MyData table. Again, for this switching, SQL Server just updates the page references,
and partition 5 will contain new loaded data in just a few milliseconds. As before, we set the
next used filegroup for ps_Quaterly_RangeRight .
Finally, we again verify the number of partitions, and the number of rows in partitions, by
querying the sys.partitions system catalog view. Note that partition 5 now contains
the 100000 records that we just loaded from the staging table.
While switching between tables with the ALTER TABLE…SWITCH command,
the schema of the corresponding table and partition must be identical.
There's more...
This recipe is an example of a basic sliding window and provides a solution for archiving/
deleting old data, removing old partitions, and loading new data in new partitions, periodically.
Based on this, you can implement a solution in your production environment to perform the
same task at regular intervals. This can be weekly, monthly, quarterly, yearly, or at any time
interval that you choose. The important thing is your script should be intelligent and dynamic
enough to create and remove partitions on the fly, appropriately, by setting range values
dynamically. This way you can delete a large amount of data from your data store in small
amount of time and load new bulk data in very large tables without locking the table for
long time.