更改SQL Server中的非聚簇索引以添加更多包含的列 [英] Alter Non Clustered Index in SQL Server to Add more included columns

问题描述

是否可以更改现有的非聚集索引以包含更多列作为涵盖列的一部分。

Is it possible to alter an existing non clustered index to include more columns as a part of Covered columns.

例如。

ALTER INDEX IX_NC_TableName_ColumnName
FOR TableName(ColumnName)
INCLUDE(Col1, Col2, Col3)

想在上面的索引中包含 Col4 。

Want to include Col4 in above index.

添加此列会有什么影响？是否存在碎片或其他任何内容？

What will be the impact of adding this column? Will there be fragmentation or anything else?

推荐答案

额外包含列的成本将增加存储空间并可能出现碎片。由于叶节点大小增加（假设密钥不是增量的），并且对新包含列的更新增加了长度，碎片与旧索引相比会略有增加。

The cost of an additional included column will be increased storage and potentially fragmentation. Fragmentation will increase slightly compared to the old index due to the increased leaf node size (assuming keys are not incremental) and if updates to the new included column increases length.

考虑使用CREATE INDEX ... WITH DROP EXISTING到此任务。这将避免丢弃旧索引并避免排序，利用现有索引键序列进行重建：

Consider using CREATE INDEX...WITH DROP EXISTING to this task. This will avoid dropping the old index and avoid a sort, leverage the existing index key sequence for the rebuild:

CREATE INDEX IX_NC_TableName_ColumnName
ON TableName(ColumnName)
INCLUDE(Col1, Col2, Col3, Col4)
WITH(DROP_EXISTING = ON);

这篇关于更改SQL Server中的非聚簇索引以添加更多包含的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！