更改SQL Server中的非聚簇索引以添加更多包含的列

问题描述：

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

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中的非聚簇索引以添加更多包含的列

相关推荐