How to Partition an existing SQL Server Table

By:   |   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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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

View all my tips



Comments For This Article




Tuesday, May 31, 2022 - 6:52:54 AM - Manish Kumar Hate Back To Top (90124)
Thank you so much for great explanation.. would you please do this partition by range. I am really want to know how we can do it for partition by range. Thanks in advance

Monday, February 14, 2022 - 3:56:53 PM - mitch Back To Top (89789)
Great article but is missing some pretty important details. The article's intent was to show how to and FK to a partitioned table. Which it clearly does, kind-of. What is does not mention is that partition switching will not work. The table and indexes are not aligned. The table is partitioned but the primary key is not. The only way to get the primary key aligned is to associate it with the partition scheme and to do that the partitioning column has to be added to the index. Then trying to add a FK fails. Full 360 degree circle and back to the start. HTH someone. Mitch

Friday, July 5, 2019 - 11:31:42 AM - Amit Kumar Gupta Back To Top (81686)

Thanks for the nice article.

I just have a question that I have a large table in the PRIMARY file group. If I create a seperate filegroup and partition scheme is create on the new file group and followed by the Clustered index on the Partition scheme.

My question is what will happen to the data in this case, All the data for the table will move to the ne file group?

Please suggest

amit


Sunday, December 23, 2018 - 3:14:07 PM - Luis Azario Back To Top (78544)

I saw that ther is a wizard for partitioning, I am using Sql-Server 2014, can't find it. 


Thursday, October 5, 2017 - 3:01:33 PM - dschwie Back To Top (66938)

Excellent post. So helpful. Thanks so much.


Saturday, October 1, 2016 - 1:35:52 AM - Rajashekar Back To Top (43471)

It was useful.... Thanks..... 

 


Thursday, July 14, 2016 - 9:49:27 AM - Mike Back To Top (41887)

Correct me if i am wrong but you are NOT Partition an Existing Sql Server table. 

You are partition the indexes of a table.

In order to create a table on a partition you need to specify the Partition scheme during creation of a table. 

I Cant do this with just an ALTER statement:

 

CREATE TABLE [Log].[Event] (

[Id]INT IDENTITY(1,1) NOT NULL,

...

) ON [LogPartitionScheme] ([HostUtcDate]);

 

 


Wednesday, January 20, 2016 - 1:59:14 PM - darlove Back To Top (40458)

To all people out there: Please, do not put non-clustered indexes on a table first and then a clustered index because if you do that, you'll have to rebuild the non-clustered indexes, so you'll be doing part of the work twice. The correct way to do it is to first put the cluster on and then all the non-clustered indexes. This is because NC indexes without exception include the key(s) of the cluster (which is the pointer to the actual row in the table). Bear in mind that a clustered index actually IS the table itself.


Thursday, December 24, 2015 - 4:56:21 AM - Ekrem Onsoy Back To Top (40305)

"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."

If it wasn't an Enterprise Edition, you wouldn't be able to create Partitioned Tables at all... Partitioning itself is an Enterprise Edition feature.


Wednesday, November 18, 2015 - 12:08:40 PM - TM Back To Top (39096)

Regarding partitioning an existing table, with a good amount of data about 37 mil rows in an OLTP Database.  We attempted to try rebuilding the clustered index with the partition scheme we created but it doesn't allow an online rebuild because of some Varchar (max) fields in the table. So the next solution was to create a new table with the new partition scheme, copy the data over and rename it, the problem we ran into was that the FK references from other tables also got changed to reference the Old table when it was renamed, we had to rereate these as well.  Does anyone have any other idea of how to avoid dropping and recreating the FK references?


Wednesday, July 1, 2015 - 9:58:31 PM - Jorge Back To Top (38101)

Hi:

   I would like to manage a daily partition in a table. I mean, everyday create the partition for the next working day and drop the partition for the prev working day (just keep 2 days in database). I read all the examples on internet, and all show examples with partitions previously defined. Does exists something about this idea?

 

Thanks for your comments.

 

Jorge 


Thursday, June 4, 2015 - 9:49:17 PM - Ben Snaidero Back To Top (37740)

@Binu

You could partition the shared tables on the customer column.  I assume there is some sort of id to identify the customers.

Thanks for reading.


Thursday, June 4, 2015 - 6:56:01 AM - Binu Back To Top (37415)

Thanks for the article,

I have a question, I have hosted Azure Sql server database,I am planning to host an application for two customers,some tables are shared between these customers but some tables are exclusive say example "Transction" how can I use partition for this concept?

 

Thanks

Binu


Tuesday, October 28, 2014 - 2:09:52 AM - Hany Helmy Back To Top (35089)

First of all thank you very much for the informative article,

secondly I have this issue in my business environment (both testing & production), i can`t simply drop my existing PK indx as it is being referenced by too many other tables as foreign key constraints, so I have to drop those too many keys first which is just not practical @ all & time consuming as well.

So I guess the only applicaple solution for that situation is to create a new table with the same keys & constraints (which is not easy task) then copy the data (over 7 million rows)- this will be a big headache, it took 16 min in a testing instance.

So if you have a better idea, please share.

Thanx

Hany


Thursday, January 2, 2014 - 9:43:21 AM - Ben Snaidero Back To Top (27937)

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.


Tuesday, December 31, 2013 - 10:16:12 AM - Mark Davis Back To Top (27918)

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

 


Tuesday, October 8, 2013 - 4:09:20 PM - Jim Back To Top (27084)

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


Thursday, October 3, 2013 - 6:23:01 PM - Ben Snaidero Back To Top (27031)

@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


Wednesday, October 2, 2013 - 6:30:48 PM - Jeff Roughgarden Back To Top (27018)

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?

 

 

 


Friday, June 28, 2013 - 8:22:37 AM - henrik staun poulsen Back To Top (25613)

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, May 1, 2013 - 3:06:51 PM - Srinath Back To Top (23667)

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


Tuesday, March 26, 2013 - 9:42:03 AM - the sqlist Back To Top (23016)

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.


Tuesday, March 26, 2013 - 8:20:43 AM - ananda Back To Top (23015)

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


Thursday, March 21, 2013 - 5:36:21 AM - VS Back To Top (22929)

good one......


Friday, February 22, 2013 - 4:14:22 AM - Yadav Back To Top (22352)

Very Nice Example


Thursday, February 21, 2013 - 12:05:18 PM - Nira Back To Top (22344)

Thank you ,

It is clear and simple.

Nira















get free sql tips
agree to terms