Alter Index Add Column – Workaround – SQL in Sixty Seconds #097

During the recent Comprehensive Database Performance Health Check, I identified at my client’s place that we needed to modify one of the indexes and add a couple of additional columns to improve its performance. However, while modifying it we faced a unique challenge that we can’t actually alter index to add columns. Let us discuss that today.

Alter Indexes to Add Column – Not Possible

While SQL Server indeed supports the ALTER INDEX syntax, it allows it for the making changes to various storage properties like fill factor, compression etc. However, it does not allow you to add or remove columns from the index. If you want to alter index, you technically first drop the index and create the new index.

This is fine if you have to modify one or two indexes, but if you want to modify many indexes or if your primary job is SQL Server Performance Tuning, this process will become very cumbersome process after a while. It is also not convenient to create every time DROP and CREATE script and run them in a single transaction.

Fortunately, SQL Server has a very simple and effective solution for us. You can add the index with a keyword DROP_EXISTING = ON.

Let me show you that in this quick SQL in the Sixty Seconds video.

Here is the script which I had used in the video.

USE [WideWorldImporters]
GO
CREATE NONCLUSTERED INDEX [FK_Sales_Orders_ContactPersonID] ON [Sales].[Orders]
(
	[ContactPersonID] ASC
)
INCLUDE([PickingCompletedWhen]) WITH (DROP_EXISTING = ON) ON [USERDATA]
GO

I hope this will save you time and makes life easier. Please do let me know if you have any questions or suggestions. I am always looking for more ideas to build interesting videos for all of you. You can follow my channel on YouTube.

Additional Reading:

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

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