Different Types of Partitioning Operations in SQL Server 2005

In this post let me explain about the three different types of Operations one can do with Partitions. They are:

1. Split Partition
2. Merge Partition
3. Switch Partition (Important of the lot)

Before reading further, make sure that you have read my earlier posts. That is, this and this.

Split Partition

For splitting a partition we need to make use of “Alter Partition Function” syntax. So in our existing “Partition function” lets create a new range with boundary value “Jan 01, 1970”.

Alter Partition Function PF_DOB_Range()
Split Range ('01-01-1970')

Now if we execute this code snippet it would throw an error something like this:

Msg 7707, Level 16, State 1, Line 1
The associated partition function 'PF_DOB_Range' generates more partitions than there are file groups mentioned in the scheme 'PS_DOB_1'.

So the way to split a partition is:

Step 1: Create a new Filegroup (if at all already you don’t have an extra filegroup)
Step 2: Make use of that Filegroup while altering the Partition Scheme.
Step 3: Add a “File” to the newly created Filegroup.
Step 4: Now execute the above “Alter Partition Function”

Step 1: Create a new Filegroup (if at all already you don’t have an extra filegroup)

Alter Database VadivelTesting Add Filegroup FileGrp3
Go

Step 2: Make use of that Filegroup while altering the Partition Scheme.

Alter Partition Scheme PS_DOB_1
Next Used FileGrp3;
Go

Step 3: Add a “File” to the newly created Filegroup.

Alter Database VadivelTesting
Add File
(
Name = Vel2006NewFile,
Filename = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Vel2006NewFile.ndf',
Size = 1MB,
Maxsize = 100MB,
Filegrowth = 1MB
)
To FileGroup FileGrp3

Verifying Purpose

If it is mentioned as "Range Right" then it works like this:

1. All records whose DOB is < Jan 1, 1960 would fall into Partion 1
2. All records whose DOB is >= Jan 1, 1960 and < Jan 1, 1970 would fall on Partion 2
3. All records whose DOB is >= Jan 1, 1970 and < Jan 1, 1980 would fall on Partion 3
4. All records whose DOB is >= Jan 1, 1980 would fall under Partition 4.

Till now there were only 3 partitions for dbo.empDetails table. Now that we have added another new partition execute the below code to see the new partition which got created.

Select * from dbo.EmpDetails Where $PARTITION.PF_DOB_Range(DateOfBirth) = 4

Merge Partition

If for some reasons we need to MERGE a partition then its also possible. We need to make use of the same syntax which we used for “Splitting partitions” and just replace the word “Split” with “Merge” :)

Alter Partition Function PF_DOB_Range()
Merge Range ('01-01-1970')

After executing the above statement we would be having the same old 3 partitions as we saw in this post.

Switch Partition

Extract from MSDN

Switches a block of data in one of the following ways:

· Reassigns all data of a table as a partition to an already-existing partitioned table.
· Switches a partition from one partitioned table to another.
· Reassigns all data in one partition of a partitioned table to an existing non-partitioned table.

Lets try to move the data in one of the already existing partition to a new table. For ex: Lets move Partition 3 of dbo.EmpDetails table to a dbo.EmpDetails_StagingTable.

--- Create a new table with the same structure of EmpDetails table.
Create Table EmpDetails_StagingTable
(
EmpID int identity,
EmpName Varchar(25),
DateOfBirth datetime,
Salary int
)
Go

---Try to switch the records in Partition 3 into this.
Alter table dbo.EmpDetails Switch Partition 3 To dbo.EmpDetails_StagingTable;
Go

If we execute the above statement. It would throw an error something like this:

Msg 4939, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. table 'VadivelTesting.dbo.EmpDetails_StagingTable' is in filegroup 'PRIMARY' and partition 3 of table 'VadivelTesting.dbo.EmpDetails' is in filegroup 'FG3'.

Hope the error message is self-explanatory.

::Tip 1

To overcome this we need to create that staging table on the same filegroup of Partition 3 (or what ever partition you are trying to move).

Drop table dbo.EmpDetails_StagingTable
Go

Create Table EmpDetails_StagingTable
(
EmpID int identity,
EmpName Varchar(25),
DateOfBirth datetime,
Salary int
)
on FG3;

::Tip 2

The staging table needs to be empty if we plan to SWITCH records from a partition to this. For example lets us insert the below record into that table and then try to Swtich partition.

Insert into dbo.EmpDetails_StagingTable values ('V','1976-03-23',500)
Go

Alter table dbo.EmpDetails Switch Partition 3 To dbo.EmpDetails_StagingTable;
Go

Msg 4905, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. The target table 'VadivelTesting.dbo.EmpDetails_StagingTable' must be empty.

So delete that record and then try this.

Delete from dbo.EmpDetails_StagingTable
Go

Alter table dbo.EmpDetails Switch Partition 3 To dbo.EmpDetails_StagingTable;
Go

-- To verify whether the records have been moved from Partition 3 to this newly created table.
Select * from dbo.EmpDetails_StagingTable;

Records in all other partitions would be there as-is except for this 3rd partition. You can verify it by executing this line.

Select * from dbo.EmpDetails Where $PARTITION.PF_DOB_Range(DateOfBirth) = 3