-2

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.

6

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
2

No, this isn't possible.

To add, remove, or change the position of an index column, you must drop and recreate the index.

Source

That being said, you can use the below option to automatically drop and recreate the index:

DROP_EXISTING = ON

Please see the CREATE INDEX page for more details on the syntax.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.