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

In this recipe, we will learn how to efficiently delete a large amount of data belonging to one
quarter of the year, and load a large amount of data for a whole quarter into an existing table
by splitting, merging, and switching partitions.

Assume that we need to store data on a quarterly basis and that data belonging to each
quarter is stored in a partition. On a quarterly basis, we need to purge data for the oldest
quarter and load new data for the latest quarter. For example, in our partitioned table we
have partitions to contain data for the following quarters:

F Quarter 1: January 2011 to March 2011
F Quarter 2: April 2011 to Jun 2011
F Quarter 3: July 2011 to September 2011
F Quarter 4: October 2011 to December 2011

We want to delete data for quarter 1(January 2011 to March 2011) and load new data into
a new partition for quarter 1 (January 2012 to March 2012). For this, we can use the date
and time column in the table to implement table partitioning in such a way that the partition
contains data for one quarter. We can merge two partitions or split an existing partition by
introducing a new boundary range value in the partition function. We will delete the data and
partition for an older quarter by merging the partition, and will introduce a new partition by
splitting the last partition. For this, initially we will have the following partitions:

PARTITION-2
(DATA)
JAN-11 TO
MAR-11

PARTITION-3
(DATA)
APR-11 TO
JUN-11

PARTITION-4
(DATA)
JUL-11 TO
SEP-11

PARTITION-5
(DATA)
OCT-11 TO
DEC-11

PARTITION-1
(EMPTY)
< JAN-11

PARTITION-6
(EMPTY)
JAN-12 TO
MAR-12

This example will give you a foundation to implement a sliding window scenario. In a sliding
window, we purge the oldest data from a partition by switching it to a staging table and
truncating the staging table. In our case, partition 2 (the oldest quarter) will be switched to
a staging table and truncated. After deleting data, partition 2 will also be empty, and we will
merge it with partition 1. So, the total number of partitions will become five from six. The
following figure shows how partition switching and merging will occur: