Partitioning- PART 2: Managing Partition using SPLIT, MERGE and SWITCH

In PART 1 I showed how to partition a table and the steps involved with examples. In this section i will show how you can manage partitions using SPLIT, MERGE and SWITCH.

SPLIT: is used to add new boundary points to a partition. The syntax is:
ALTER PARTITION FUNCTION PartitionFunctionName()
{
SPLIT RANGE (boundary_value)
}

Example 1:
Alter Partition Function PFN_Example()
Split Range (60)

I am using the partition function i created in PART 1. The above example adds another partition to the table. So any data greater than 60 in the table will be moved to the new partition.

MERGE: is used to remove a boundary point from the partition. The syntax is same as SPLIT. Replace SPLIT with MERGE.

ALTER PARTITION FUNCTION PartitionFunctionName()
{
MERGE RANGE (boundary_value)
}
Example 2:
Alter Partition Function PFN_Example()
MERGE Range (10)

The number of partitions for the table decreases by one by running the query in example 2. The data that existed in this partition is combined into a single partititon.

SWITCH: add rows to a table and removes rows from a table.

An INSERT or DELETE operation can be used to perform the same action. Then why use SWITCH? SWITCH is used because it does not cause blocking and lets you add any number of rows in less than a second.

Restrictions on a SWITCH operation:

1. A Full partition must be switched with an empty Partition

2. Both tables must use the same partition function and partition scheme.

3. Both tables must have the same structure for tables as well as indexes

4. The range of values in the partition being switched must not exist in the target table.

Syntax for SWITCH operation:

ALTER TABLE table_name
SWITCH [ PARTITION source_partition_number_expression]
TO [ schema_name.] target_table
[ PARTITION target_partition_number_expression]

DATA FLOW:

Let’s look at the flow of data when loading new data and removing old data. I have taken the concept from SQL SERVER DEEP DIVES, a book that I would highly recommend.

Below are the steps to load new data into a Partitioned Table.

1. Add a staging table in the same database and load the table with the new Data.

2. Add an Empty Partition at the front (Where you want to load the data) using SPLIT as shown in the figure below:

3. Use SWITCH to swap empty partition with Staging table. The data is now transferred to Partition 3, and staging table is empty as shown in the figure below:

This operation involves metadata only, so it only takes milliseconds to complete irrespective of the size of the table.

Below are the steps to remove old data from a Partitioned Table.

1. Create an empty staging table in the same database

2. Swap the data with the old data using SWITCH operation. The result is shown below:

The STAGING table now contains the old data and the partition 1 is empty.

3. MERGE the Old Empty Partition with Partition 2 or another empty partition and effectively disappear. The staging table if needed can be copied or archived and the table truncated and reused later.

SUMMARY: Partitioning is quiet an involved topic, I just went through the basics in two Parts:

PART 1: CREATING PARTITIONS

PART 2 : MANAGING PARTITIONS USING SPLIT SWITCH AND MERGE.

I hope you will find this useful.
For more information on Partitioning please refer SQL Server Books Online.Also see http://msdn.microsoft.com/en-us/library/ms345146.aspx for a full discussion on Table Partitioning.