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%'
objectname |
indexname |
partition_id |
partition_number |
rows |
---|---|---|---|---|
TABLE1 | PK_TABLE1 | 72057594042712064 | 1 | 999 |
TABLE1 | IX_TABLE1_col2col3 | 72057594042777600 | 1 | 999 |
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.
objectname |
indexname |
partition_id |
partition_number |
rows |
---|---|---|---|---|
TABLE1 | IX_TABLE1_partitioncol | 72057594043039744 | 1 | 233 |
TABLE1 | IX_TABLE1_partitioncol | 72057594043105280 | 2 | 365 |
TABLE1 | IX_TABLE1_partitioncol | 72057594043170816 | 3 | 366 |
TABLE1 | IX_TABLE1_partitioncol | 72057594043236352 | 4 | 35 |
TABLE1 | IX_TABLE1_col2col3 | 72057594043301888 | 1 | 999 |
TABLE1 | PK_TABLE1 | 72057594043367424 | 1 | 999 |
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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips