Skip to main content

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

Comments

Anil said…
good article please join us at sqlcon we need your expertise there as well

just send an email to sqlcon-subscribe@yahoogroups.com
Gaurav said…
Hi!
We are Eagerly Awaiting your Next Post.....
Anonymous said…
Hi,
Why No posts for a looong time ??
:)
Anonymous said…
Hi,

Your article is really useful for us. How to partition the existing table with relationship keeping the partition key as the column dtCreaion(datetime) and the primary key as ID(Int).
Anonymous said…
Thank you for explaining partitions whith a helpful example.Can you also put some lights on list partition in the same fashion?
Anonymous said…
Hi there,
Amazing....!

You are providing great help to the SQL community.

Thanks.
Anonymous said…
Hi Vadivel!
Your article is very useful for my analysis. Thank you very much.

Cheers!
Jako Vijay.
Anonymous said…
Hello!

This was a wonderful post. I would like to add that in the switch partition, if the source table had a clustered index created on it then the staging table must also have the same.

Thanks,
GBM

Also, other helpful post was:
http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=cf223632-c93b-4242-b0f2-af493e051266
Anonymous said…
Hello.

Taking your demo further, how can I switch in data into partition #3 which you already switched out. I have been trying to do just that but it is coming up with different error messages. The staging table I have from which I am trying to pull data has same structure with regard to fields, constraint, index, etc. However, it is just not working out.

Thanks,

shaht.

Popular posts from this blog

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul

AWS fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request

While using AWS and trying to copy a file from a S3 bucket to my EC2 instance ended up with this error message. Command Used: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ Error: fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request The error goes off if we add the region information to the command statement. I am using Asia Pacific (Mumbai) so used ap-south-1 as the region name. Modified Command: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ --region ap-south-1

[Non Tech] Want to know the recipe for Omelette :)

Fed up with Bread - Jam and Curd Rice, today i wanted to eat Omelette. Interesting part is I wanted to cook it myself :) So in the first picture you see all the items which are needed for preparing an Omelette. When I had a closer look at the eggs I see that almost all the eggs are broken. But believe me when I bought it couple of days back it was in perfect condition! I was wondering whether the eggs have become rotten or pretty old to consume! I tried taking an egg and break it but couldn't break it at all :) Since I have kept in the freezer all the eggs have frozen and looked like a iron ball :) After trying for few minutes of trying i removed the shell of the egg and then kept that iron ball :) into a bowl and placed it within Oven. I heated it for 1 minute and checked. It melted only to a limit. So i just set it for another 2 minutes and checked it later. It has melted but the part of the egg white has become a Omelette :( I didn't leave it there. I took the bowl out of