Modify an index in SQL Server without dropping it-CodePudding

I want to modify an existing index and add some columns in the INCLUDE section. This is a pretty big table and the index would take a while to run.

Is there a way to write the index statement so that if I have to cancel the index modification the existing index won't be dropped?

CodePudding user response：

You can use the CREATE INDEX...WITH(DROP_EXISITING = ON) syntax to change an existing index in order to add addition key or included columns. This will leverage the existing index definition to avoid sorting when creating the new index.

For example, with the original index definition:

CREATE INDEX idx ON dbo.YourTable(col1)
    INCLUDE(col2);

This will add an included column::

CREATE INDEX idx ON dbo.YourTable(col1)
    INCLUDE(col2, col3)
    WITH(DROP_EXISTING = ON);

If you are using Enterprise Edition, consider the ONLINE = ON option too to avoid a schema modification lock on the table for the duration of the operation.