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