Wednesday, November 15, 2006

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

9 comments:

khaqsar 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).

anindya 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.