SQL: Partition switching in SQL Server 2005

http://www.techrepublic.com/blog/datacenter/partition-switching-in-sql-server-2005/143

Partition switching in SQL Server 2005
2Comments<http://www.techrepublic.com/blog/datacenter/partition-switching-in-sql-server-2005/143#talkback>
more +

* Email
* Print
* Add to Favorites
* Del.icio.us
* Digg
* Hacker News
* LinkedIn
* Reddit
* Technorati
By Tim Chapman<http://www.techrepublic.com/search?q=tim+chapman>
July 23, 2007, 11:21 AM PDT

Takeaway: Tim Chapman demonstrates how you can modify defined partitions in SQL Server 2005 so that you can easily and efficiently add new data.

Last week, I showed how you can use the new table and index partitioning features in SQL Server 2005 to split your table data across one or more filegroups<http://blogs.techrepublic.com.com/datacenter/?p=139>. In this article, I demonstrate how you can modify those defined partitions so that you can easily and efficiently add new data.

Partitioning

Partitioning is the separation of database rows from one large table into one or more smaller tables. There can be performance and administrative advantages in using partitioning because you are essentially using one or more tables as one large table. These advantages can be even greater when you take into account the ability to easily add and delete data to and from your partitions.

Partition switching

Partition functions are used to map table field values to defined partitions. These partitions are initially defined when the function is created. However, it is sometimes necessary to change how your partitions are set up to accommodate new data, move current data, or delete data. Partition switching is the process of moving a block of data around in one or more tables.

An example
One common use for partition is the archiving of data, such as moving data from your OLTP database to your data warehouse for reporting purposes. This example looks at the importing of MP3 product data into a SalesHistoryArchive table. For the purpose of this example, assume that a list of MP3Player product sales have been imported into the database and placed in a table named MP3Import.

This example is built upon the partitioning example I illustrated in my article on creating table partitions in SQL Server 2005<http://blogs.techrepublic.com.com/datacenter/?p=139>. The following script recreates the objects I used in that article. These objects include the partition function, partition scheme, and the SalesHistoryArchive table.

CREATE PARTITION FUNCTION [pf_Product_Partition](VARCHAR(10)) AS RANGE LEFT

FOR VALUES (N'BigScreen', N'Computer', N'PoolTable')

Go



CREATE PARTITION SCHEME [ps_Product_Scheme] AS PARTITION [pf_Product_Partition]

ALL TO ([PRIMARY])

GO



IF OBJECT_ID('SalesHistoryArchive')>0

DROP TABLE [SalesHistoryArchive];

GO

CREATE TABLE [dbo].[SalesHistoryArchive]

(

[SaleID] [int] IDENTITY(1,1),

[Product] [varchar](10) NULL,

[SaleDate] [datetime] NULL,

[SalePrice] [money] NULL

)ON [ps_Product_Scheme](Product)

GO



DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=10000)

BEGIN

INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)

VALUES('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))

INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)

VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))

INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)

VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))

SET @i = @i + 1

END

GO

Now that I have data to use for the example, I can dig into the details of the new product import. The MP3Player product records that I will be importing will be placed in the SalesHistoryArchive table. This table is currently partitioned, and I want the MP3Player product to be placed on its own partition. To accomplish this, I will need to alter the partition function that I use on the SalesHistoryArchive table.

ALTER PARTITION FUNCTION [pf_Product_Partition] ()

SPLIT RANGE ('MP3Player')

Run the following query to verify that a new partition has been added for the SalesHistoryArchive table:

SELECT * From sys.partitions

WHERE OBJECT_NAME(object_id) = 'SalesHistoryArchive'

At this point, data can be inserted directly into the SalesHistory archive table, and any product of MP3Player will be placed in its own partition. However, data is inserted into the database from some type of external source and placed in its own individual database table. One great feature of partitioning in SQL Server 2005 is that it allows you to switch data from one table into another table very easily and quickly. It is so fast because the switching of the partition data only involves the changing of SQL Server's internal metadata, so no data is actually moved. The script below creates the MP3Import table, which I will assume has been imported into the database from an external data source.

CREATE TABLE [dbo].MP3Import

(

[SaleID] [int] IDENTITY(1,1),

[Product] [varchar](10) NULL,

[SaleDate] [datetime] NULL,

[SalePrice] [money] NULL

)ON [ps_Product_Scheme](Product)

GO

ALTER TABLE MP3Import

ADD CONSTRAINT ck_Product CHECK(Product = 'MP3Player')

GO

There are a couple of specific things to notice in the script above. First, the table structure of the MP3Import table is the same as the SalesArchiveHistory table, and the MP3Import table uses the same partitioning scheme as the SalesHistoryArchive table. Using the same partitioning scheme isn't really necessary to complete what I want to complete, but I think it makes it easier. Next, I am creating a check constraint on the table to ensure that only the MP3Player product is imported into the table. This will ensure that when I switch the data that all of the data goes into the exact partition that I want.

The next step is to load some data into the import table:

DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=10000)

BEGIN

INSERT INTO MP3Import(Product, SaleDate, SalePrice)

VALUES('MP3Player', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))

SET @i = @i + 1

END

GO

I want to switch the data that is in the MP3Import table to the SalesHistoryArchive table. In previous versions of SQL Server, this would entail using an INSERT statement to add the data to the table. With SQL Server 2005, I can simply use the SWITCH statement to move the data pointers to point to the SalesHistoryArchive table. In the script below, I alter the MP3Import table and switch the data that resides in partition 3, where the data was initially inserted, to partition 3 on the SalesHistory archive table.

ALTER TABLE MP3Import

SWITCH PARTITION 3 TO SalesHistoryArchive PARTITION 3

The following query shows that the SalesHistoryArchive table now contains 10000 records in partition 3. Because the data in the MP3Import table is no longer needed, I can simply issue a DROP command on the table to remove it from the database.

DROP TABLE MP3Import

Because I have associated the data pointers with the new partition in the SalesHistoryArchive table, I can safely remove the MP3Import table.

To switch or not to switch

This article explores one scenario where it is advantageous to use partitioning to easily add data to your partitoned tables. It is worth noting that data can be removed as easily as it is added when the full partition is involved. While partitioning is a great advantage when it is suitable, it is not appropriate for all circumstances. Only through testing and analysis can you determine if partitioning, and thus partitioning switching, can improve your overall database scenario.