在SQL 2005中更改聚簇索引（PK）的最佳方法 [英] Best way to change clustered index (PK) in SQL 2005

问题描述

我有一个表，它在两列上有一个聚簇索引 - 表的主键。
它定义如下：

I have a table which has a clustered index on two columns - the primary key for the table. It is defined as follows:

ALTER TABLE Table ADD  CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED 
(
  [ColA] ASC,
  [ColB] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

我想删除这个聚簇索引PK并添加如下的聚簇索引，并使用非聚集索引添加主键约束，如下所示。

I want to remove this clustered index PK and add a clustered index like follows and add a primary key constraint using a non-clustered index, also shown below.

CREATE CLUSTERED INDEX [IX_Clustered] ON [Table] 
(
  [ColC] ASC,
  [ColA] ASC,
  [ColD] ASC,
  [ColE] ASC,
  [ColF] ASC,
  [ColG] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,     DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

ALTER TABLE Table ADD CONSTRAINT
  PK_Table PRIMARY KEY NONCLUSTERED 
  (
    ColA,
    ColB
  ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

我打算删除PK聚簇索引，然后添加新聚簇索引，然后添加非聚集主键索引，但我了解到删除现有聚簇索引会导致表数据重新排序（请参阅此处的答案） 当我删除群集主节点时会发生什么SQL 2005中的关键），我认为不应该这样做。该表正在敲1 TB，所以我真的想避免任何不必要的重新排序。

I was going to just drop the PK clustered index, then add the new clustered index and then add the non-clustered primary key index, but I learned that dropping the existing clustered index would cause the table data to be reordered (see answer here What happens when I drop a clustered primary key in SQL 2005), which I don't think should be necessary. The table is knocking 1 TB, so I really want to avoid any unnecessary reordering.

我的问题是，从现有结构到所需结构的最佳方法是什么结构？

My question is, what is the best way to go from the existing structure to the desired structure?

编辑：只是想澄清一下。该表是1TB，遗憾的是我没有空间来创建临时表。如果有办法在没有创建临时表的情况下这样做，请告诉我。

Just want to clarify. The table is 1TB and I unfortunately do not have space to create a temporary table. If there is a way to do it without creating a temp table then please let me know.

推荐答案

如果你的桌子起床了大小为1 TB，可能有很多行，我强烈建议不要使聚集索引更胖！

If your table is getting up to 1 TB in size and probably has LOTS of rows in it, I would strongly recommend NOT making the clustered index that much fatter!

首先，删除并重新创建聚集索引将至少对您的所有数据进行一次洗牌 - 仅此一项将花费很长时间。

First of all, dropping and recreating the clustered index will shuffle around ALL your data at least once - that alone will take ages.

其次，您尝试创建的大型复合聚簇索引将显着增加所有非聚集索引的大小（因为它们包含每个叶节点上的整个聚簇索引值，用于书签查找）。

Secondly, the big compound clustered index you're trying to create will significantly increase the size of all your non-clustered indices (since they contain the whole clustered index value on each leaf node, for the bookmark lookups).

问题是：为什么你想这么做？难道你不能只用这些列添加另一个非聚集索引来覆盖你的查询吗？为什么这必须是聚集索引？我没有看到任何优势....

The question is more: why are you trying to do this?? Couldn't you just add another non-clustered index with those columns, to potentially cover your queries? Why does this have to be the clustered index?? I don't see any advantage in that....

有关索引的更多信息，尤其是聚簇索引争论，请参阅 Kimberly Tripp的博客 - 非常有帮助！

For more information on indexing and especially the clustered index debate, see Kimberly Tripp's blog on SQL Server indexes - very helpful!

Marc

这篇关于在SQL 2005中更改聚簇索引（PK）的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！