Change a Primary Key from Nonclustered to Clustered

Question

Suppose I have an SQL Server 2005 table, TableX, with 2 indexes on it:

PK_TableX = PRIMARY KEY NONCLUSTERED on FieldA
IX_TableX_FieldB = CLUSTERED on FieldB

I want to switch the PK to be CLUSTERED, and the other index to be NONCLUSTERED.

I have to assume that the database will be in use at the moment I try to change the indexes round - so my primary concern that I want to avoid, is that at some point in the process the PK constraint will not exist on the table. I want to be protected against any risk of duplicate keys being inserted.

i.e. I can't just drop the primary key and recreate it.

This process needs to be done via an SQL script, not via SSMS.

I have an approach which I think will work (I'll post it as a potential answer), but would like to open it up in case I'm missing something or there is another/better way. Plus, it may prove useful for others in the future

Answer 1

1) Drop the existing clustered index first (IX_TableX_FieldB):

   DROP INDEX TableX.IX_TableX_FieldB

2) Create a (temporary) UNIQUE constraint on the unique fields referenced in the primary key

    ALTER TABLE TableX
    ADD CONSTRAINT UQ_TableX UNIQUE(FieldA)

3) Drop the PRIMARY KEY

    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 back on as NONCLUSTERED

   CREATE NONCLUSTERED INDEX IX_TableX_FieldB ON TableX(FieldB)

Answer 2

What if I have an autoincrement INT as my non-clustered primary key, and there are about 25 foreign keys defined to it ? (I know.. the original designer was braindead...)

This is a 15M row table, on a live database, SQL Standard, so dropping indexes is out of the question. Even temporarily dropping the foreign key constraints will be difficult.

I'm curious if anybody has a solution that causes minimal downtime.