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

sql-server indexing database-performance
Shantanu Gupta picture Shantanu Gupta · Apr 29, 2015 · Viewed 17.7k times · Source

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

Dan Guzman picture Dan Guzman · Apr 29, 2015

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

Related questions

What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More
List of all index & index columns in SQL Server DB

How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is: select s.name, t.name, i.name, c.name from sys.tables t inner join sys.schemas s on …

Read More
Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More