将主键从Nonclustered更改为Clustered [英] Change a Primary Key from Nonclustered to Clustered

问题描述

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

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

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

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

我想将PK切换为CLUSTERED，另一个索引为NONCLUSTERED。

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

我必须假设数据库将在我尝试更改索引时使用 - 所以我想要避免的主要问题是，在过程中某些时候PK约束不会存在于桌面上。我希望能够防止插入重复密钥的风险。

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.

此过程需要通过SQL脚本完成，而不是通过SSMS。

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

推荐答案

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

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

   DROP INDEX TableX.IX_TableX_FieldB

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

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）删除PRIMARY KEY

3) Drop the PRIMARY KEY

    ALTER TABLE TableX
    DROP CONSTRAINT PK_TableX

4）重新设置PRIMARY KEY为CLUSTERED

4) Recreate the PRIMARY KEY as CLUSTERED

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

5）删除临时UNIQUE约束

5) Drop the temporary UNIQUE constraint

   ALTER TABLE TableX
   DROP CONSTRAINT UQ_TableX

6）添加IX_TableX_F ieldB重新开启为NONCLUSTERED

6) Add the IX_TableX_FieldB back on as NONCLUSTERED

   CREATE NONCLUSTERED INDEX IX_TableX_FieldB ON TableX(FieldB)

这篇关于将主键从Nonclustered更改为Clustered的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！