Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an existing table with data:

dbo.Test (col1,col2,col3....) ON [PRIMARY]

I need to change this table to be partitioned like this:

dbo.Test(col1,col2,col3....) ON Ps_Date(Col2)

How I can I achieve this without dropping and recreating the table?

share|improve this question
    
Do you already have all the prep work completed, as in is your partition function, scheme, and 'staging' table setup already? Kin and Sebastian's post shows how to do that very well. Which step are you on? –  Ali Razeghi Aug 13 '13 at 2:47

2 Answers 2

up vote 9 down vote accepted

To partition a table, you can follow below brief steps :

  • first create a partition function and partition scheme
  • After that you can partition a table.
  • IF your table has a clustered index, then you need to drop and recreate it on the right partition or you can use DROP_EXISTING clause to re-create the clustered index.
  • If your table does not have a clustered index, then you can just create one on the right partition using the partition scheme.
  • Also Enterprise Edition has the flexibility of using the ONLINE=ON option of the CREATE INDEX statement to minimize any downtime for your application. Note that you will see a performance degradation while the index is being rebuilt using the ONLINE option.

TO automate partitioning, you can use SQL Server Partition Management utility or SQL Server Partitioned Table Framework available on codeplex as well.

Some Good resources :

share|improve this answer
    
I lol'ed at "brief steps". Nothing about partitioning says brief to me! Apart from that, great answer! –  Max Vernon Aug 13 '13 at 0:57
1  
@MaxVernon I did not include scripts, so I wrote Brief :-) –  Kin Aug 13 '13 at 1:16
    
Kin, your answer reminded me of Tripp's partitioning segment we ended up at together last year in Seattle. Thanks for the brief post! :P –  Ali Razeghi Aug 13 '13 at 2:49
    
@PaulWhite Reworded the answer ! Thanks for spotting .. Need to be more specific next time .. :-) –  Kin Aug 13 '13 at 13:23

You do not specify if your table has a clustered index or not, so let's walk through all options.

I am going to use this example partition function, partition scheme and table:

CREATE PARTITION FUNCTION pf1(INT) AS RANGE LEFT FOR VALUES(10,20,30,40);
GO
CREATE PARTITION SCHEME ps1 AS PARTITION pf1 ALL TO ([PRIMARY])
GO
CREATE TABLE dbo.pt(pc INT NOT NULL, id INT NOT NULL) ON [PRIMARY];
GO

1. Your table has a clustered index that was not created by a constraint.

This is the easiest case. You can just use the CREATE INDEX statement with the DROP_EXISTING clause to move the table to the partition scheme.

Assume for the example that this clustered index had been created:

CREATE UNIQUE CLUSTERED INDEX ptc ON dbo.pt(Id) ON [PRIMARY];

To partition this table, the clustered index has include the partition column (pt in our case) as part of the key. This statement changes the clustered index to include the partition column and partitions it at the same time:

CREATE UNIQUE CLUSTERED INDEX ptc ON dbo.pt(pc, Id) WITH(DROP_EXISTING = ON)ON ps1(pc) ;

The DROP_Existing clause automatically removes the existing index before creating the new one. This is preferred over a separate DROP INDEX as it causes the nonclustered indexes to be rebuild only once.

2. Your table has a clustered index that is part of a PRIMARY KEY or UNIQUE constraint and it contains the partition column as part of the key

This one is still easy and very similar to the previous one.

Assume this PRIMARY KEY constraint had been created on the table:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc PRIMARY KEY CLUSTERED (pc, Id) ON [PRIMARY];

Now you can just run the same re-creation script that we used in 1:

CREATE UNIQUE CLUSTERED INDEX ptc ON dbo.pt(pc, Id) WITH(DROP_EXISTING = ON)ON ps1(pc) ;

3. The table has a clustered index that does not include the partition column but was created as part of a PRIMARY KEY or UNIQUE constraint

Tough luck. You cannot change the definition of a PRIMARY KEY or UNIQUE constraint after the fact. Your only option is to drop the constraint and then either recreate it including the partition column or create a clustered index independent of the constraint that includes the partition column. In the second case you can re-create the constraint NONCLUSTERED without including the partition column. Because now this constraint is not aligned (meaning its supporting index is not partitioned) you have to specify where to place it on disk.

Assume the table had a primary key like this:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc PRIMARY KEY CLUSTERED (Id) ON [PRIMARY];

To partition this table you have to drop the constraint first:

ALTER TABLE dbo.pt DROP CONSTRAINT ptc;

Then you need to create the partitioned clustered index:

CREATE UNIQUE CLUSTERED INDEX ptci ON dbo.pt(pc, Id) ON ps1(pc) ;

If you choose to re-create the PRIMARY KEY constraint non-aligned you can do so like this:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

4. Your table does not have a clustered index

In this case it is recommended in most cases to just create a clustered index to establish the partitioning. You can use the previously seen create index statement for that:

CREATE UNIQUE CLUSTERED INDEX ptci ON dbo.pt(pc, Id) ON ps1(pc) ;

However, if you have a good reason to not create a clustered index you can get away with the following two-step approach. Sadly there is no direct way to make this change.

Assume your table does not have a clustered index. To partition the table you need to first create a CLUSTERED UNIQUE constraint. (You could also use a CLUSTERED PRIMARY KEY constraint). If you have a column combination that is unique that is a simple step:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc UNIQUE CLUSTERED(pc,id);

After the constraint is created you can drop it again and "move" the table to the new partition scheme at the same time:

ALTER TABLE dbo.pt DROP CONSTRAINT ptc WITH(MOVE TO ps1(pc));

If you do not have a column combination that is unique, you are out of luck. In this case your only option is to add a new column and fill it with unique values. If the table is reasonably small you could do something like this:

ALTER TABLE dbo.pt ADD tmp_id INT IDENTITY(1,1);

However, that will take an exclusive table lock until all rows are valued. Depending on the table size this can be for quite a while. After that column is created follow the above two steps to first create the UNIQUE constraint and then drop it again right away. Afterwards you can also drop the column again. All these steps are fairly intrusive, so you are probably better of to just create a clustered index on the table. That does not even have to be unique.


If you have Enterprise Edition, you can use the WITH(ONLINE=ON) clause on most of the statements above. That will keep your table available to other connections. However, there will be a performance impact during that time.

share|improve this answer
    
Thanks Sabastian...Excellent Explanation...Nice –  343 Aug 14 '13 at 1:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.