How to Partition an existing SQL Server Table

By: Ben Snaidero   |   Comments (26)   |   Related: 1 | 2 | 3 | 4 | > Partitioning

Problem

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.

Solution

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

--Table/Index creation
CREATE TABLE [dbo].[TABLE1] 
([pkcol] [int] NOT NULL,
 [datacol1] [int] NULL,
 [datacol2] [int] NULL,
 [datacol3] [varchar](50) NULL,
 [partitioncol] datetime)
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (pkcol) 
GO
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) 
  ON [PRIMARY]
GO
-- Populate table data
DECLARE @val INT
SELECT @val=1
WHILE @val < 1000
BEGIN  
   INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, partitioncol) 
      VALUES (@val,@val,@val,'TEST',getdate()-@val)
   SELECT @val=@val+1
END
GO

Looking at the sys.partitions system view we can see we have created a regular single partition table.

SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%TABLE1%'

SQL Server Partitioned Table Creation

In order to create a partitioned table we'll need to first create a partition function and partition scheme. For our example we are going to partition the table based on the datetime column. Here is the code to create these objects and check some of their metadata in the system views.

CREATE PARTITION FUNCTION myDateRangePF (datetime)
AS RANGE RIGHT FOR VALUES ('20110101', '20120101','20130101')
GO
CREATE PARTITION SCHEME myPartitionScheme 
AS PARTITION myDateRangePF ALL TO ([PRIMARY]) 
GO
SELECT ps.name,pf.name,boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id

Now that we have a partition scheme we can go ahead and partition our table. Since we are going to partition the table using a clustered index and our table already has a clustered index defined we'll need to drop this index first and recreate the constraint using a non-clustered index. If our table did not have a clustered index we could omit this step and just run the CREATE CLUSTERED INDEX statement. Similarly, if our table had a clustered index defined, but it was defined on same column that we plan to partition the table on we could run the CREATE CLUSTERED INDEX statement with the DROP_EXISTING clause. Finally, if you are concerned about the downtime required to perform this task and you are using SQL Server Enterprise Edition you could use the ONLINE=ON option of the CREATE INDEX statement to minimize any downtime for your application. Keep in mind that you may see some performance degradation while the index is being rebuilt using the ONLINE option. Here is the script that we can use in our scenario.

ALTER TABLE dbo.TABLE1 DROP CONSTRAINT PK_TABLE1
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY NONCLUSTERED  (pkcol)
   WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.TABLE1 (partitioncol)
  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
  ON myPartitionScheme(partitioncol)
GO

Once this statements completes we can again take a look at the sys.partitions system view (see code above) and confirm our table now has 4 partitions.

Sample SQL Server Table and Data Cleanup

--cleanup
DROP TABLE TABLE1
DROP PARTITION SCHEME myPartitionScheme
DROP PARTITION FUNCTION myDateRangePF

Next Steps

• Read more tips on partitioning
• Use partitions to archive data from SQL Server
• Read more on other types of partitioning

About the author

Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips