How to partition an existing non-partitioned table 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?
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.