How to Alter Index to Add New Columns in SQL Server? – Interview Question of the Week #278

Question: How to Alter Index to Add New Columns in SQL Server?

Answer: It is not possible to Alter Index definition to add or remove columns. You need to use CREATE INDEX with the DROP_EXISTING clause to perform these operations.

During Comprehensive Database Performance Health Check, as a final step of the consulting engagement, we look at the indexes. While indexes are not the primary contributor to the performance increase, they are often major reasons or reduction in the performance. (Video).

While working on the index optimization phase, we often do index consolidation. In this exercise, we take all the existing indexes and organize them in the most efficient possible way. Sometimes we add a column to the existing index and sometimes we remove a column from the existing index.

The most common issue which I encountered during this time is to alter index to add new columns or remove columns. The alter command which is available in SQL Server can be used to rebuild or reorganize the indexes with few options. However, if you want to add or remove columns from your index, you have to alternatives.

Alternative 1: Drop the earlier index and recreate it with the same name.

Alternative 2: Create an index with the same name but use keyword DROP_EXISTING.

USE [WideWorldImporters]
GO
CREATE NONCLUSTERED INDEX [IX_Sales_Invoices_OrderID] ON [WideWorldImporters].[Sales].[Invoices]
(
[OrderID] ASC
)WITH (DROP_EXISTING = OFF)
GO

As you can see in the previous example, when you use DROP_EXISTING, SQL Server will recreate the index just like altering the index. If during the index creation there is an error as this is done in a single transaction, SQL Server automatically rolls it back to the original index.

I am very curious to know do you face similar issues as I face? Any other SQL Server issue would you like me to cover in this segment of Interview Questions, if yes, please drop a note in the comments area.

Reference: Pinal Dave (https://blog.sqlauthority.com)