Sql-server – Can a non-clustered index be transformed to a covering index in SQL Server

covering-indexnonclustered-indexsql server

I have a non-clustered index which includes one column. Now I want to alter the index and add additional two columns as covering columns. Is it possible? If yes, then can you, please, provide the T-SQL script.

Best Answer

You can't do that using the ALTER INDEX command. But you can recreate the index using either DROP-CREATE commands or DROP_EXISTING option:

DROP INDEX [IDX_Name] ON [dbo].[YourTable]
GO

CREATE NONCLUSTERED INDEX [IDX_Name] ON [dbo].[YourTable]
(
    [Field1] ASC
) INCLUDE ([Field2],[Field3])
GO

or

CREATE NONCLUSTERED INDEX [IDX_Name] ON [dbo].[YourTable]
(
    [Field1] ASC
) INCLUDE ([Field2],[Field3]) WITH (DROP_EXISTING = ON)
GO