How to Partition an existing SQL Server Table
|By: Ben Snaidero | Read Comments (1) | Related Tips: More > Partitioning|
I have read many articles on SQL Server partitioning and how to create a partitioned table, but I have an existing SQL Server database that has a few very large tables that could benefit from partitioning. What are the steps required to partition an already existing table? Check out this tip to learn more.
There are two different approaches we could use to accomplish this task. The first would be to create a brand new partitioned table (you can do this by following this tip) and then simply copy the data from your existing table into the new table and do a table rename. Alternatively, as I will outline below, we can partition the table in place simply by rebuilding or creating a clustered index on the table.
Sample SQL Server Table and Data to Partition
CREATE TABLE [dbo].[TABLE1]
([pkcol] [int] NOT NULL,
[datacol1] [int] NULL,
[datacol2] [int] NULL,
[datacol3] [varchar](50) NULL,
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (pkcol)
CREATE NONCLUSTERED INDEX IX_TABLE1_col2col3 ON dbo.TABLE1 (datacol1,datacol2)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)