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

I know this is old but this will script out all the FK drops, the pk drop, the pk recreate, the FK recreates. Replace MYTABLE with your table name.

   IF  EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[FKAgainstTableList]')) BEGIN      DROP TABLE FKAgainstTableList END --CREATE TABLE FKAgainstTableList (ForeignKey VARCHAR(30),[Table] VARCHAR(30)) DECLARE @PKTableName VARCHAR(100),          @PKName varchar(100),         @FKName varchar(100),         @sql varchar(max),         @PKcolumnName varchar(30),         @table VARCHAR(100),         @FKColumnName VARCHAR(100),          @parentColumnNumber int SET @PKTableName = 'MYTABLE' set @PKName = (SELECT name FROM sys.indexes WHERE OBJECT_NAME(object_id) = @PKTableName AND is_primary_key = 1) set @PKcolumnName = (SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = @PKTableName AND is_identity =1) PRINT @PKcolumnName   SELECT  OBJECT_NAME(sys.foreign_key_columns.parent_object_id) [Table],sys.columns.name [FKColumnName],sys.foreign_keys.name [FKName]      INTO FKAgainstTableList     FROM sys.foreign_keys INNER JOIN sys.foreign_key_columns      ON sys.foreign_keys.object_id = sys.foreign_key_columns.constraint_object_id     INNER JOIN sys.columns ON sys.columns.object_id = sys.foreign_keys.parent_object_id AND sys.columns.column_id = sys.foreign_key_columns.parent_column_id     WHERE OBJECT_NAME(sys.foreign_keys.referenced_object_id) = @PKTableName   DECLARE table_cur1 CURSOR  FOR     SELECT  * FROM FKAgainstTableList      PRINT @sql  -------------------------------Disable constraint on FK Tables OPEN table_cur1 FETCH NEXT FROM table_cur1 INTO @table,@FKColumnName,@FKName WHILE   @@FETCH_STATUS = 0     BEGIN         SET @sql ='ALTER TABLE '+@table+' DROP CONSTRAINT '+ @FKName         PRINT @sql         FETCH NEXT FROM table_cur1 INTO @table,@FKColumnName,@FKName     END CLOSE table_cur1 DEALLOCATE table_cur1 --------------------------------DROP AND recreate CLUSTERED pk IF  EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(@PKTableName) AND name = @PKName) BEGIN     SET @sql = 'ALTER TABLE '+@PKTableName+' DROP CONSTRAINT '+ @PKName     PRINT @sql  END SET @sql = 'ALTER TABLE '+@PKTableName +' ADD  CONSTRAINT '+@PKName+' PRIMARY KEY CLUSTERED ('+@PKcolumnName+' ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]' PRINT(@sql)  --------------------------------Enable FK constraints on FK tables. DECLARE table_cur2 CURSOR  FOR     SELECT  * FROM FKAgainstTableList OPEN table_cur2 FETCH NEXT FROM table_cur2 INTO @table,@FKColumnName,@FKName WHILE   @@FETCH_STATUS = 0     BEGIN         SET @sql = 'ALTER TABLE '+@table+' WITH NOCHECK ADD  CONSTRAINT  '+ @FKName+' FOREIGN KEY(['+@FKColumnName+'])         REFERENCES ['+@PKTableName+'] (['+@PKcolumnName+'])'         PRINT(@sql)         SET @sql = 'ALTER TABLE '+@table+' CHECK CONSTRAINT  '+@FKName         PRINT(@sql)          FETCH NEXT FROM table_cur2 INTO @table,@FKColumnName,@FKName           END CLOSE table_cur2 DEALLOCATE table_cur2 DROP TABLE FKAgainstTableList