indexing - Alter Non Clustered Index in SQL Server to Add more included columns

Question

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

e.g.

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

Want to include Col4 in above index.

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

Answer 1

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.

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);