如何加快重新创建群集索引 [英] How to speed up recreating cluster index
问题描述
在SQL Server中，如果我想向群集索引定义中添加一个新列，则没有更改群集索引的选项.唯一的选择是删除并创建具有新定义的群集索引.
In SQL Server, there is no option for altering the cluster index if i want to add one new column to cluster index definition. The only option is to drop and create cluster index with new definition.
据我了解，对于大容量表，删除和创建集群索引是非常昂贵且耗时的.
From what I understand, drop and create of cluster index is a very costly and time consuming for high volume tables.
集群索引重新创建会重建表上的所有非集群索引，这可能会非常昂贵.
Cluster index recreate rebuilds all the nonclustered indexes on a table which can be very expensive.
该论坛的问题无论如何，我们是否可以加快群集索引的重新创建"
The question to this forum "is there anyway we can speed up cluster index recreating"
我可以想到的一种解决方法是在重新创建群集索引之前删除所有非群集索引.这种方法行得通吗?
The one workaround what I can think is to drop all non-cluster index before recreating cluster index. Will this approach work ?
推荐答案
使用
CREATE .... WITH (DROP_EXISTING = ON)
代替
DROP ...
CREATE ...
这意味着非聚集索引仅需更新一次(以包括新的键列).不会两次-首先使用物理摆脱，然后再次使用新的CI密钥.
This means the non clustered indexes only have to be updated once (to include the new key column). Not twice - first to use the physical rid and then again to use the new CI key.
DROP_EXISTING 子句告诉SQL Server已经删除了现有的聚集索引，但是将在其位置添加一个新的聚集索引，从而使SQL Server推迟更新非聚集索引，直到新的聚集索引就位为止.
The DROP_EXISTING clause tells SQL Server that the existing clustered index is being dropped but that a new one will be added in its place, letting SQL Server defer updating the nonclustered index until the new clustered index is in place..
此外，如果聚簇索引键不变并且定义为UNIQUE，SQL Server根本不会重建非聚簇索引，将聚簇索引定义为UNIQUE并不会带来明显的性能优势.
Additionally, SQL Server won't rebuild the nonclustered index at all if the clustered index key doesn't change and is defined as UNIQUE, which isn't an obvious performance benefit of defining a clustered index as UNIQUE
示例
CREATE TABLE #T
(
A INT,
B INT,
C INT
)
CREATE CLUSTERED INDEX IX ON #T(A)
CREATE CLUSTERED INDEX IX ON #T(A,B) WITH (DROP_EXISTING = ON)
DROP TABLE #T
这篇关于如何加快重新创建群集索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
