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

Question: Can ALTER INDEX add a column to an existing index? No. To change its key or included columns, recreate the index definition. CREATE INDEX ... WITH (DROP_EXISTING = ON) lets you do that using the existing index name.

Index consolidation is often a final step in my Comprehensive Database Performance Health Check. We examine overlapping indexes and decide whether a useful column belongs in an existing definition. Adding another index every time a query needs something is an easy way to leave a table with expensive baggage.

That is where this question comes up: why can’t I simply alter the column list? ALTER INDEX can rebuild, reorganize and change supported options. It doesn’t replace the key or INCLUDE list.

A complete, safe example

This uses a private temporary table. First the index contains only OrderID. The second statement replaces it with two key columns and one included column:

-- A private temporary table, not an existing application's index.
IF OBJECT_ID('tempdb..#IndexColumnsDemo') IS NOT NULL
    THROW 50001, 'The example temp table already exists.', 1;
CREATE TABLE #IndexColumnsDemo
(
    OrderID int NOT NULL,
    InvoiceDate date NOT NULL,
    CustomerID int NOT NULL
);
CREATE NONCLUSTERED INDEX IX_Order
ON #IndexColumnsDemo (OrderID);

-- Replace that index definition using its existing name.
CREATE NONCLUSTERED INDEX IX_Order
ON #IndexColumnsDemo (OrderID, InvoiceDate)
INCLUDE (CustomerID)
WITH (DROP_EXISTING = ON);

SELECT i.name AS index_name, c.name AS column_name,
       ic.key_ordinal, ic.is_included_column
FROM tempdb.sys.indexes AS i
JOIN tempdb.sys.index_columns AS ic
  ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN tempdb.sys.columns AS c
  ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID('tempdb..#IndexColumnsDemo')
  AND i.name = N'IX_Order'
ORDER BY ic.is_included_column, ic.key_ordinal, c.name;
DROP TABLE #IndexColumnsDemo;

The final query should list OrderID with key ordinal 1, InvoiceDate with key ordinal 2, and CustomerID with is_included_column = 1. Included columns are available at the leaf level; they aren’t an additional search-key order.

The detail that makes it work

DROP_EXISTING = ON is essential when replacing the existing index. The earlier example used OFF, which creates an index and fails if that name already exists. It did not demonstrate the replacement described in the article.

You can also drop and recreate separately, but then you have two separate statements and an interval without that index. For an ordinary index replacement, the single CREATE INDEX operation handles the replacement as one operation. A failure isn’t a reason to run an independent drop first.

Script the existing definition before editing. Keep the required uniqueness, filter, filegroup or partition placement, compression and other options. Indexes enforcing PRIMARY KEY or UNIQUE constraints have additional restrictions; this temporary-table example isn’t a procedure for changing a constraint’s key.

Changing key order and adding wide included columns can affect reads, writes, storage and maintenance. Measure the workload rather than assuming a wider index is automatically better. My article about an index reducing SELECT performance explains why the definition deserves attention.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.