SQL 2005中更改聚集索引（主键）的最佳方法

Question

我有一张表格，其中有两列的聚集索引——该表的主键。它定义如下：

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，并添加一个如下的聚集索引，使用非聚集索引添加主键约束，也在下面显示。

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聚集索引，然后添加新的聚集索引，再添加非聚集主键索引，但我了解到删除现有的聚集索引会导致表数据重新排序（请参见此处的答案What happens when I drop a clustered primary key in SQL 2005），我认为这不是必要的。该表占用了1TB的空间，因此我真的希望避免任何不必要的重新排序。

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

编辑：只是想澄清一下。该表占用了1TB的空间，我不幸没有创建临时表的空间。如果有一种不需要创建临时表的方法，请告诉我。

- Mr. Flibble

Answer 1

这不是完整的回答，但请确保如果您在表上有任何其他索引，请先删除它们。否则，当您删除聚集索引然后再添加新的聚集索引时，SQL Server 将不得不重新构建它们。一般的步骤如下：

1. 删除所有非聚集索引
2. 删除聚集索引
3. 添加新的聚集索引
4. 添加回所有非聚集索引

- Tom H.

---

好的，那是我的最初计划，但是移除聚集索引的第二步会导致所有数据被移动。我希望能够以某种方式合并第2步和第3步，以避免不必要的数据移动。 - Mr. Flibble

Answer 2

如果你的表格大小已经达到1TB，并且可能有大量的行，我强烈建议不要将聚集索引变得更加庞大！

首先，删除和重新创建聚集索引至少会使所有数据重新排序一次 - 这本身就需要很长时间。

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

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

有关索引特别是聚集索引的争论的更多信息，请参见SQL Server索引的Kimberly Tripp's blog - 非常有用！

Marc

- marc_s

Answer 3

1. Create a new table:

   CREATE TABLE newtable (colA INT, colB INT)
   

   • Insert all values from the old table into the new table:

     INSERT INTO newtable SELECT * FROM table

   • Drop the old table:

     DROP TABLE table

   • Rename the new table to the old table

     EXEC sp_rename 'newtable', 'table'

   • Build the indexes:

     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]

- Quassnoi

---

那不是一个选项。我有一个1TB的表格在一个1.5TB的RAID上。 - Mr. Flibble

SQL Server需要为重新排序和移动分配空间。如果您的空间不足，可以在DROP INDEX中使用MOVE TO选项，将表创建在临时存储上，然后再次在RAID上重新创建它。 - Quassnoi

你可以将行分块传输，边传输边从源中删除。但这可能是一个非常缓慢的过程。 - Tom H

好的...为什么创建一个临时表比删除聚集索引和允许不必要的重新排序要更好呢?看起来这样做会同样缓慢并且需要更多的工作。 - Mr. Flibble

请查看此处如何估算您的新表大小：http://msdn.microsoft.com/zh-cn/library/ms187445.aspx - Quassnoi

Answer 4

聚集索引实际上并不改变表中存储的数据的物理顺序。自从 SQL 6.5 以来，它就不再是这样了。

页面上的数据按正确顺序存储。页面可以以任何物理顺序存储在磁盘上。

- mrdenny

---

这是真的吗？我原本以为数据的物理顺序是由聚集键决定的。 - Mitch Wheat

页面上的数据按正确顺序存储。页面可以以任何物理顺序存储在磁盘上。 - mrdenny

你可能想要更新你的答案，以包括这个区别。 - Mitch Wheat