Changing primary key index structure

Changing the structure of a primary key constraint index from nonclustered to clustered (or from clustered to nonclustered) is not necessarily as straight forward as it first seems. The process of changing it over, involves the constraint being dropped and then recreated. This could potentially cause a problem if you’re making the change on a table whilst there could be activity against it.

Example
TableX was originally created as below:

CREATE TABLE [TableX]  
(  
    FieldA INTEGER CONSTRAINT PK_TableX PRIMARY KEY NONCLUSTERED,  
    FieldB DATETIME NOT NULL,  
    FieldC VARCHAR(50)  
)  
GO  
CREATE CLUSTERED INDEX IX_TableX_FieldB ON TableX(FieldB)  
GO

After a period of time, it becomes clear that performance would be better if the primary key was made to be clustered instead, and the existing clustered index switched to nonclustered. The following script demonstrates how to make the switch, in a manner that prevents the primary key being violated while it is switch over, by creating a temporary unique constraint.

-- 1) Drop the existing CLUSTERED index  
DROP INDEX TableX.IX_TableX_FieldB  
  
-- 2) Create a (temporary) UNIQUE constraint on the unique fields referenced in the primary key. This will enforce the uniqueness when we drop the PK.  
ALTER TABLE TableX  
ADD CONSTRAINT UQ_TableX UNIQUE(FieldA)  
  
-- 3) Drop the existing nonclustered PRIMARY KEY constraint.  
ALTER TABLE TableX  
DROP CONSTRAINT PK_TableX  
  
-- 4) Recreate the PRIMARY KEY as CLUSTERED  
ALTER TABLE TableX  
ADD CONSTRAINT PK_TableX PRIMARY KEY CLUSTERED(FieldA)  
  
-- 5) Drop the temporary UNIQUE constraint  
ALTER TABLE TableX  
DROP CONSTRAINT UQ_TableX  
  
-- 6) Add the IX_TableX_FieldB index back on as NONCLUSTERED  
CREATE NONCLUSTERED INDEX IX_TableX_FieldB ON TableX(FieldB)

This scenario is a good reason why I always avoid SQL Server’s autogenerated constraint names!

See also