Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

How to Partition an existing SQL Server Table

MSSQLTips author Ben Snaidero By:   |   Read Comments (12)   |   Related Tips: More > 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


Last Update: 2/21/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, February 21, 2013 - 12:05:18 PM - Nira Read The Tip

Thank you ,

It is clear and simple.

Nira


Friday, February 22, 2013 - 4:14:22 AM - Yadav Read The Tip

Very Nice Example


Thursday, March 21, 2013 - 5:36:21 AM - VS Read The Tip

good one......


Tuesday, March 26, 2013 - 8:20:43 AM - ananda Read The Tip

very good example... I

I want asking one question about table partition? if table does not have datatime column , which column need to take for partition? Can I take primary key column?

thanks


Tuesday, March 26, 2013 - 9:42:03 AM - the sqlist Read The Tip

The problem with this example is that it creates the partition on the PRIMARY file group and if the PRIMARY file group has only 1 file all 4 partitions will be created on that file. In order to really benefit of the flexibility and the real break of a large table it is recommendable to use dedicated filegroups and files for each partition.

Another thing is tht if the table has an HML or spatial index you cannot do an ONLINE index rebuild so creating a new file and inserting the data would be the preferred way.


Wednesday, May 01, 2013 - 3:06:51 PM - Srinath Read The Tip

Well written !! Thanks for such a great article, Ben !!!


Friday, June 28, 2013 - 8:22:37 AM - henrik staun poulsen Read The Tip

When you partition tables, IMHO, you want to ensure that all index are alligned, i.e. they use the same partition function.

Otherwise you cannot use partition switching (which is one of the major benefits of partitioning) 

 


Wednesday, October 02, 2013 - 6:30:48 PM - Jeff Roughgarden Read The Tip

Thank you for writing this article and the previous one on archiving using partitioning. Between the two of them, I was able to set up partitions with aligned indexes on both TransactionHistory and TransactionHistoryArchive in AdventureWorks2008R2, and archive data nicely.

Of course, it all runs very quickly on this sample database. At the company for which I'm consulting, there is a still-growing table with over 2 billion rows that consumes well over a TB of data for the table alone. The DB is OLTP hosted on SQL Server 2008R2 Enterprise Edition. To partition the Transaction table, I will need to:

 

 

 

  1. Create the partition function (which sets the date boundaries)
  2. Create the partition scheme (which specifies how the partitions are to be stored)
  3. Drop existing non-clustered indexes (since a new primary key, PK, is to be defined)
  4. Drop the PK (and clustered index) on TransactionID
  5. Create a new non-clustered PK on (TransactionID, TransactionDate)
  6. Create a new clustered index on TransactionDate (which is the partitioning column)
  7. Re-create the NC indexes dropped in item 3.
  8. Create a check constraint on the minimum TransactionDate (needed to enable switching)

 

Items 1,2, and 8 are just about immediate. But I wonder how long items 3 to 7 will take when being done online (to the extent possible) and what the performance hit will be. Do you have any experience to shed light on these questions?

 

 

 


Thursday, October 03, 2013 - 6:23:01 PM - Ben Snaidero Read The Tip

@Jeff

Unfortunately I do not have any experience with tables that large.  The drop statements should be immediate as well but I would suggest testing the create statements to see what impact you can expect that is if you have an environment to est in

Thanks for reading

Ben


Tuesday, October 08, 2013 - 4:09:20 PM - Jim Read The Tip

Just what I needed to partition a really large table "in place". Thanks Ben!


Tuesday, December 31, 2013 - 10:16:12 AM - Mark Davis Read The Tip

Greetings, Ben, and thank you.  If you don't mind revisiting this topic again, I have an issue you might have an answer for: 

I re-built my clustered index on the partition key, and my table is now partitioned.  However, a difference between the above scenario and mine is that I had a partition scheme that mapped all of my partitions (730 for two years of daily snapshots) to individual file groups.  So, now what I have is a very large PRIMARY file group and original file, plus the partitioned rows in the 730 new file groups.  I expected SQL Server to remove the rows from PRIMARY.

Of note, the row counts from dm_db_partition_stats appear to be associated with the other indexes on the table (JOINed via sys.indexes.data_space_id).  This is all in our development environment, but I haven't tried dropping the indexes yet.  Do you have any guidance or insight related to this?

Thanks!

Mark Davis

 


Thursday, January 02, 2014 - 9:43:21 AM - Ben Snaidero Read The Tip

Hi Mark,

You just need to drop and recreate your indexes.  See the following link for more details.

http://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx

Thanks for reading.

Ben.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.