Today’s post will demonstrate how to partition an existing table without disturbing the existing data in the table by taking advantage of it’s clustered index.

SQL Server Books Online (BOL) is laced with interesting tidbits of information but sometimes falls short of delivering on the specifics.  Let’s face it, there’s no way to demonstrate every combination of options and switches available.  With every new release of SQL Server the number of option combinations is growing exponentially.

Our post today is premised off the following statement in BOL for the ALTER TABLE command:

You can drop the clustered index and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option.

In this case, as far as I can tell, the details of HOW to move the table to another filegroup or partition scheme is left as an exercise for the reader.  Since I’m the reader and I like to exercise, here we go…

Before diving into the details, here’s the high level route we will take:

1	Create a new database with a single file group.  This is straight out of the manual… RIGHT CLICK ==> CREATE DATABASE
2	Create a table and populate it with data.  We’ll use a copy of the AdventureWorks.Person.Address table.  The key to this working is that the table has a clustered index on it.
3	Setup the partitioning function and scheme.
4	Use ALTER TABLE to move the clustered index to the new partition scheme.

For starters I created a test database called SwitchTest.  It is a stock, out of the box db right from SSMS.  The key point is that all data resides on the PRIMARY filegroup.

Now we’ll create a table that’s modeled after the AdventureWorks.Person.Address table and populate it with the data. 

USE SwitchTest;
GO

CREATE TABLE dbo.[2B_Partitioned] (
        [AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
        [AddressLine1] [nvarchar](60) COLLATE Latin1_General_CS_AS NOT NULL,
        [AddressLine2] [nvarchar](60) COLLATE Latin1_General_CS_AS NULL,
        [City] [nvarchar](30) COLLATE Latin1_General_CS_AS NOT NULL,
        [StateProvinceID] [int] NOT NULL,
        [PostalCode] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL,
        [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Address_rowguid] DEFAULT (newid()),
        [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Address_ModifiedDate] DEFAULT (getdate()),
        CONSTRAINT [PK_AddressID] PRIMARY KEY CLUSTERED (
            [AddressID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY];

-- Populate the Address table from Adventure Works...
INSERT INTO dbo.[2B_Partitioned] (    AddressLine1,
                                    AddressLine2,
                                    City,
                                    StateProvinceID,
                                    PostalCode )
    SELECT    AddressLine1,
            AddressLine2,
            City,
            StateProvinceID,
            PostalCode
    FROM AdventureWorks.Person.Address;

This resulted in 19, 614 rows being loaded to our 2B_Partitioned table.  Also note the clustered index on Address_ID!

Now let’s add a second file group, FG2,  to our database.  Since my tired laptop only has a single drive I’ll create it on the same drive as the primary file group.  If this were a production system we would prefer to add the second file group to a different physical drive.

USE [master]
GO
ALTER DATABASE [SwitchTest] ADD FILEGROUP [FG2];

ALTER DATABASE [SwitchTest]
    ADD FILE (    NAME = N'FG2',
                FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FG2.ndf',
                SIZE = 2048KB ,
                FILEGROWTH = 1024KB ) TO FILEGROUP [FG2];
GO

Setting up the partitioning in SQL Server is a two step process.  Create the partition function; create the partition scheme.

USE SwitchTest;
GO

CREATE PARTITION FUNCTION Move2Part_part_funct (int) AS
    RANGE LEFT FOR VALUES (10000);
GO

CREATE PARTITION SCHEME Move2Part_part_scheme AS
    PARTITION Move2Part_part_funct TO ([Primary], [FG2]);

Since the 2B_Partitioned table has about 20,000 records, this partition function will split the data in half.  For more info on setting up partitions please refer to BOL. 

And now for the work horse of the task.  We’ll use the ALTER TABLE to drop the clustered index and in the same query, move the data to the partition scheme:

USE SwitchTest;
GO

SET STATISTICS TIME ON

ALTER TABLE dbo.[2B_Partitioned]
    DROP CONSTRAINT [PK_AddressID]
    WITH (MOVE TO Move2Part_part_scheme ([AddressID]));

How do we know we just partitioned the data?  Look at it!

SELECT    $partition.Move2Part_part_funct([AddressID]) AS [Partition Num],
        count(*) AS [Row Cnt]
FROM    dbo.[2B_Partitioned]
GROUP BY $partition.Move2Part_part_funct([AddressID])
ORDER BY $partition.Move2Part_part_funct([AddressID]);

Using the $partition command we can see that we have 10,000 rows in the first partition and the remaining 9,xxx in the second partition:

The last step towards making this table production ready is to re-create the primary key:

ALTER TABLE dbo.[2B_Partitioned]
    ADD CONSTRAINT [PK_AddressID] PRIMARY KEY ([AddressID]);

CONCLUSION:  SQL Server offers a myriad number of ways to get a job done.  Today we demonstrated how to use a table’s clustered index to convert a non-partitioned table to a partitioned table.  The secret of the task is the WITH (MOVE) clause of the ALTER TABLE command.

For a historical and detailed discussion on table and index partitioning please review Kimberly Tripp’s white paper.

Happy SQL-ing!