A Quick Look at the DROP_EXISTING Option in SQL Server

In SQL Server, we can use the DROP_EXISTING option of the CREATE INDEX statement to modify an existing index. It allows us to drop and rebuild an index in one atomic operation. This can be particularly useful when we need to make changes to an index, such as altering its columns, changing its properties, or rebuilding it with different options like fill factor or sort order.

What Can We Do with `DROP_EXISTING`?

We can use DROP_EXISTING to modify an index by automatically dropping it and recreating it with the new definition. This is done in the CREATE INDEX statement, and so it saves us from having to use a separate DROP INDEX statement before the CREATE INDEX statement.

In particular, we can:

Rebuild the index
Add or drop columns
Modify options
Modify column sort order
Change the partition scheme or filegroup.

Example

Here’s an example that demonstrates how to use the DROP_EXISTING option in SQL Server. First we’ll create a table and add an index to it. Then we’ll update the index using CREATE INDEX with the DROP_EXISTING option.

1. Create a Sample Table and Index

First, let’s create the initial table and index:

-- Create a sample table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    DepartmentID INT
);

-- Create a non-clustered index on the LastName column
CREATE INDEX IX_Employees_Name
ON Employees (LastName);

2. Modify the Index Using `DROP_EXISTING`

Now suppose we want to include the FirstName column in the index to improve the performance of queries that filter on both LastName and FirstName. We can use the DROP_EXISTING option to achieve this without needing to manually drop the index first:

-- Modify the existing index to include the FirstName column
CREATE INDEX IX_Employees_Name
ON Employees (LastName, FirstName)
WITH (DROP_EXISTING = ON);

This script drops the existing IX_Employees_Name index and recreates it with the additional column FirstName. All done in an atomic operation.

Alternative Syntax

There are a couple of ways we can use DROP_EXISTING. We can use either of the following:

WITH (DROP_EXISTING = ON);
WITH DROP_EXISTING;

The first method is more explicit. It’s where we explicitly set it to ON. Note that this requires parentheses around the DROP_EXISTING = ON part.
The second method is more concise. Simply by stating DROP_EXISTING, this is enough to turn it on. This is actually the backward compatible syntax.

Can and Cannots

Microsoft points out that there are certain things we can and can’t do with DROP_EXISTING:

We can change:

A nonclustered rowstore index to a clustered rowstore index.

We can’t change:

A clustered rowstore index to a nonclustered rowstore index.
A clustered columnstore index to any type of rowstore index.

Also, the Microsoft documentation has some important information around how DROP_EXISTING works in various contexts.

Benefits of `DROP_EXISTING`

The DROP_EXISTING option provides some decent benefits over dropping and recreating the index manually. Here are some of them:

Efficiency: DROP_EXISTING is generally more efficient than dropping/recreating the index manually because it performs the drop and create in a single operation, avoiding the overhead of two separate operations.
Atomic Operation: Using DROP_EXISTING ensures that the index is dropped and recreated in a single transaction. This atomicity means that if anything goes wrong during the process, the database will not be left in an inconsistent state.
Reduced Locking and Blocking: Rebuilding the index in one step can reduce the time locks are held on the table, potentially minimizing blocking compared to manually dropping and recreating the index.
Indexed Views and Clustered Indexes: If you’re dealing with a clustered index or an index on an indexed view, the DROP_EXISTING option is especially useful because dropping a clustered index would drop all associated non-clustered indexes. With DROP_EXISTING, these are rebuilt automatically.
Ease of Use: The DROP_EXISTING option simplifies the process of recreating the index, reducing the chance of errors (e.g., forgetting to recreate the index or mistyping the index name).

When to Use `DROP_EXISTING`

Here are some scenarios where the DROP_EXISTING option can come in handy:

Index Modification: When you need to change the columns included in the index or modify its options like fill factor, compression, or sort order.
Rebuilding After Maintenance: When rebuilding an index with changes, like after altering the underlying table structure.
Performance Optimization: When optimizing indexes, such as adding included columns or changing the index structure to better support queries.
Indexed Views: When you need to modify the clustered index on an indexed view, ensuring that dependent non-clustered indexes are automatically handled.

Example of When Not to Use `DROP_EXISTING`

Just because DROP_EXISTING is an amazing option, it doesn’t mean it can be used all the time. Here are some occasions where you wouldn’t want to use it:

Completely New Index: If you’re creating a new index that does not already exist, there’s no need for DROP_EXISTING (and it will result in an error instead).
Unnecessary Rebuilds: If the index does not need to be modified, dropping and recreating it might incur unnecessary overhead.

Conclusion

SQL Server’s DROP_EXISTING option is a handy feature for managing indexes efficiently. It ensures that index modifications are done atomically, preserves index statistics, and reduces the potential for errors or inconsistencies. This option should be used whenever you need to modify an existing index without incurring the overhead and risks associated with manually dropping and recreating it.

What Can We Do with DROP_EXISTING?