SQL 将主键从非集群更改为集群

Change a Primary Key from Nonclustered to Clustered

提问by AdaTheDev

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

假设我有一个 SQL Server 2005 表 TableX，上面有 2 个索引：

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

PK_TableX = FieldA 上的 PRIMARY KEY NONCLUSTERED
IX_TableX_FieldB = FieldB 上的 CLUSTERED

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

我想将 PK 切换为 CLUSTERED，将另一个索引切换为 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.

我必须假设在我尝试更改索引回合时数据库将被使用 - 所以我想避免的主要问题是在过程中的某个时刻表上将不存在 PK 约束。我希望免受插入重复密钥的任何风险。

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.

此过程需要通过 SQL 脚本完成，而不是通过 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

我有一种我认为可行的方法（我将其作为潜在答案发布），但我想打开它，以防万一我遗漏了某些东西或有另一种/更好的方法。另外，它可能在未来对其他人有用

回答by AdaTheDev

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

1）先删除现有的聚集索引（IX_TableX_FieldB）：

   DROP INDEX TableX.IX_TableX_FieldB

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

2）对主键中引用的唯一字段创建（临时）UNIQUE约束

    ALTER TABLE TableX
    ADD CONSTRAINT UQ_TableX UNIQUE(FieldA)

3) Drop the PRIMARY KEY

3) 删除主键

    ALTER TABLE TableX
    DROP CONSTRAINT PK_TableX

4) Recreate the PRIMARY KEY as CLUSTERED

4) 将 PRIMARY KEY 重新创建为 CLUSTERED

   ALTER TABLE TableX
   ADD CONSTRAINT PK_TableX PRIMARY KEY CLUSTERED(FieldA)

5) Drop the temporary UNIQUE constraint

5) 删除临时 UNIQUE 约束

   ALTER TABLE TableX
   DROP CONSTRAINT UQ_TableX

6) Add the IX_TableX_FieldB back on as NONCLUSTERED

6) 将 IX_TableX_FieldB 添加为 NONCLUSTERED

   CREATE NONCLUSTERED INDEX IX_TableX_FieldB ON TableX(FieldB)

回答by Jerloldrartolf

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.

我知道这很旧，但这将编写所有 FK 掉落、pk 掉落、pk 重新创建、FK 重新创建的脚本。将 MYTABLE 替换为您的表名。

   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