Change a Primary Key from Nonclustered to Clustered
Solution 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)
Solution 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
Solution 3
Here's a shortcut to overwrite the original key:
CREATE UNIQUE CLUSTERED INDEX [pk_name] ON [table_name]([id])
WITH DROP_EXISTING
And as mentioned in other answers you will still need to drop/re-add foreign constraints if present.
AdaTheDev
Full-stack C#, .NET Core, Azure, SQL Server, NoSQL developer. Remote worker. Always learning.
Updated on June 18, 2021Comments
-
AdaTheDev almost 3 years
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 FieldBI 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
-
AdaTheDev about 14 yearsThis is the approach I went with in the end, seems like the best way.
-
TT. over 10 yearsThe ´set @PKcolumnName´ is not correct, it checks for an identity column and not the primary key column. It will work of course if the primary key column is the identity column.
-
TT. over 10 yearsIf you are sure there is only one primary key column, change the
set @PKColumnName
bySELECT @PKcolumnName=column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = @PKTableName
-
TT. over 10 yearsAlso note that a FILLFACTOR=80 for the PK index is often not optimal. It all depends on the table of course. In my case I ommitted the FILLFACTOR to have the default (100%).
-
FuckStackoverflow almost 9 yearsThis works. Why is it that when I try to do this using UI that it fails on the first step and re-adds the clustered index?
-
Tomas Kubes over 8 yearsIf you have dependent foreign keys or constrains, follow this dba.stackexchange.com/questions/48634/…
-
Roger Willcocks over 7 yearsDon't forget that dropping the clustered index rebuilds the entire table in the "heap" which can take a VERY long time is the table is large.
-
Kirsten almost 3 yearsto get this to work in 2019 I had to enclose @FKName in a double quotation