If you find yourself with an index in SQL Server that you no longer need, you may decide to disable it, or you may opt to get rid of it altogether. That way you can declutter your database, free up space, and perhaps help improve performance of updates to the data.

Typically, to delete an index in SQL Server, we use the DROP INDEX statement. There are cases where we might drop it via other means (for example, if it’s implemented as part of a PRIMARY KEY or UNIQUE constraint – also shown below), but DROP INDEX is usually the go to command for such operations.

Example

Here’s an example of dropping an index in SQL Server:

DROP INDEX UX_Employees_Email ON Employees;

That’s all there is to it. That dropped an index called UX_Employees_Email.

This just so happened to be a filtered UNIQUE index, which is an index that is applied to just a portion of the table. You may want to think very carefully before dropping UNIQUE indexes. Once it’s dropped, you won’t be able to guarantee unique data any more (unless you implement another measure, like a UNIQUE constraint).

PRIMARY KEY and UNIQUE Constraints

If you’re trying to drop a PRIMARY KEY or UNIQUE constraint, the above example won’t work. That’s because SQL Server prevents us from dropping indexes that are used to enforce such constraints.

Here’s what happens if we try to do that:

Msg 3723, Level 16, State 5, Line 1
An explicit DROP INDEX is not allowed on index 'Employees.UQ_Employees_Email'. It is being used for UNIQUE KEY constraint enforcement.

This is backed up by the SQL Server documentation explicitly states that:

The DROP INDEX statement doesn’t apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. To remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause.

So to drop such indexes, we need to drop the constraint itself, which might go something like this:

ALTER TABLE Employees
DROP CONSTRAINT IF EXISTS UQ_Employees_Email;

I used the IF EXISTS clause here, just in case the constraint doesn’t exist.

So just to be sure, running that code drops the UNIQUE constraint and its index. Just replace the table name and constraint name with your own.

Finding the Index Name

The above code assumes that we know the name of the index. If we don’t know the name of the index, we can always run a query like the following:

SELECT * 
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Employees');

Just replace dbo.Employees with your table name.

And if you’re looking for the name of the PRIMARY KEY or UNIQUE constraint, then the following should help:

SELECT *  
FROM sys.key_constraints
WHERE type = 'UQ'
AND OBJECT_NAME(parent_object_id) = 'Employees';

Important Considerations

It’s a really good idea to script the CREATE INDEX statement before you drop it. This will be a godsend in the event that something goes wrong and you need to quickly bring it back.

It’s also a good idea to decide whether simply disabling the index might be more appropriate than dropping it.