A useful index needs one more column, but dropping it first creates an avoidable gap. DROP_EXISTING replaces its definition through one index operation while you check the resulting access path.
Keep the Change Inside One Operation
An index change is usually a small request with a larger operational effect. An application needs another search column or an included value. The existing index already supports other queries, so its replacement deserves more care than a quick drop.
I review the full current definition before adding anything. A request to add one column should not erase an existing filter, uniqueness rule, or carefully chosen key order. The index name alone tells you very little.
CREATE INDEX with DROP_EXISTING = ON replaces an index under the same name. It avoids issuing a separate DROP INDEX before the replacement CREATE INDEX. The operation still consumes resources and follows locking rules.
ONLINE = ON is a separate option. For SQL Server installations, the online operation shown here requires an edition supporting it, such as Enterprise or Developer. Check the edition and the specific index restrictions before using that option.
SELECT SERVERPROPERTY('Edition') AS EngineEditionName,
SERVERPROPERTY('ProductVersion') AS EngineVersion;
CREATE TABLE dbo.IndexChangeDemo
(
EntryID int NOT NULL PRIMARY KEY,
AccountID int NOT NULL,
EntryDate date NOT NULL,
Amount decimal(12,2) NOT NULL
);
WITH n AS
(
SELECT TOP (20000)
ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) AS EntryNumber
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT dbo.IndexChangeDemo
SELECT CONVERT(int, EntryNumber), CONVERT(int, EntryNumber % 500),
DATEADD(day, CONVERT(int, EntryNumber % 365), CONVERT(date, '20250101')),
CONVERT(decimal(12,2), EntryNumber % 1000)
FROM n;
CREATE INDEX IX_IndexChangeDemo_Account
ON dbo.IndexChangeDemo(AccountID);Run the example in a disposable database. The requested population is sample input, not a claim about a measured server result. Catalog size influences how many rows this particular generator can supply.
Choose Keys and Included Columns Deliberately
Key columns support ordering and seek predicates. Included columns carry additional values at the leaf level. Adding an include does not make that column part of the seek key, and adding a key changes the index's ordering.
Suppose the query filters by AccountID and EntryDate, then returns Amount. AccountID followed by EntryDate supports that search pattern. Amount is a candidate include because the query returns it without filtering by it.
SELECT EntryID, EntryDate, Amount
FROM dbo.IndexChangeDemo
WHERE AccountID = 42
AND EntryDate >= '20250701'
AND EntryDate < '20250801';Capture the actual plan before changing the definition. Look for how SQL Server finds the matching rows and obtains Amount. A key lookup is evidence to interpret, not an automatic instruction to include every column.
Check other callers too. A wider index requires more pages and additional work during writes. Adding a column that one occasional report needs can impose maintenance on every transaction affecting that table.
The clustered key is already carried by nonclustered indexes where needed as a row locator. Do not add EntryID mechanically without understanding the current clustered definition. Catalog output distinguishes explicit included columns from that implicit locator.
Replace the Definition With DROP_EXISTING
Use the same table and index name in the replacement statement. Include every key and include column you want afterward. This is a complete replacement definition, not an instruction to append one item to the old list.
CREATE INDEX IX_IndexChangeDemo_Account
ON dbo.IndexChangeDemo(AccountID, EntryDate)
INCLUDE (Amount)
WITH (DROP_EXISTING = ON, ONLINE = ON);On an edition that does not support this online operation, use a planned offline operation instead. Change ONLINE to OFF after choosing an appropriate window. Do not present that edit as having the same concurrency behavior.
Online does not mean no locks. Short locking phases still occur, and long-running transactions can delay the operation. Watch concurrent workload and available space during rehearsal rather than promising uninterrupted access from the option name.
For a filtered index, preserve the intended WHERE clause in the replacement. Review data compression, fill factor, partition placement, and other settings as part of the complete definition. Omitting a setting deserves an explicit decision.
An index is quite capable of becoming wider while keeping its reassuring old name. Keep the reviewed replacement script with the change, so the name is not your only description of what now exists.
Verify the Catalog Afterward
Read the stored metadata instead of trusting a successful message. Check index type, uniqueness, filter, and each column's role. Sort keys by key_ordinal and included columns separately so the definition is easy to inspect.
SELECT i.name, i.type_desc, i.is_unique, i.has_filter,
i.filter_definition, c.name AS ColumnName,
ic.key_ordinal, ic.is_included_column
FROM sys.indexes AS i
JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c
ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID(N'dbo.IndexChangeDemo')
AND i.name = N'IX_IndexChangeDemo_Account'
ORDER BY ic.is_included_column, ic.key_ordinal, ic.index_column_id;The intended key order is AccountID followed by EntryDate. Amount should appear as an included column. Compare that output with the reviewed specification before moving on to performance checks.
I also check for duplicate indexes after a change. Replacing the named index should not leave an accidental second copy under another name. Similar definitions create write overhead without guaranteeing a separate benefit.
Compare the Query With Its Previous Plan
Run the same query using the same values, database, and connection settings. Enable the actual execution plan and STATISTICS IO. Read the seek predicates, residual predicates, and any remaining lookup operator.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT EntryID, EntryDate, Amount
FROM dbo.IndexChangeDemo
WHERE AccountID = 42
AND EntryDate >= '20250701'
AND EntryDate < '20250801';
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;The replacement gives the optimizer another access path. It does not force a seek. A broad predicate or a small table can still favor a scan, so inspect the actual choice before labeling the change unsuccessful.
Which other important query depended on the previous key order? Include that query in the comparison. Check representative writes as well, because the new definition has a maintenance cost even when the read improves.
DROP_EXISTING on Clustered and Constraint Indexes
Dropping a clustered index changes the row locator used by nonclustered indexes. Creating a replacement afterward can require another round of dependent rebuilding. DROP_EXISTING lets SQL Server handle the transition as one operation.
That does not eliminate every nonclustered rebuild. Changing the clustered key can still require updating dependent locators. Preserving the existing key avoids different work from replacing it with a new key definition.
Indexes supporting PRIMARY KEY or UNIQUE constraints have additional restrictions. Do not convert a constraint-backed unique index into a different logical rule through a routine index-edit script. Review the constraint and its referencing foreign keys first.
Keep the prior definition available and rehearse the reverse change on a copy. Confirm the resulting catalog and the important queries again. One operation makes the transition cleaner, but correctness still comes from a complete reviewed definition.
Related reading on this blog: Ins and Outs of Online Index Operations and Resumable Index Rebuilds: Pausing Maintenance Without Losing Progress.
An index replacement is not a column shortcut, it is a new access path with old responsibilities.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
