How to Disable an Index in SQL Server

Posted on by Ian

Disabling an index in SQL Server can be a useful technique to improve the performance of bulk insert operations or when you need to perform maintenance on your database.

This article provides the code/syntax required to disable an index in SQL Server.

Disable a Single Index

To disable an index in SQL Server, we use the ALTER INDEX statement. We can use this statement to disable a single index on a given table, or we can disable all indexes on the table.

Here’s how to disable a single index on the table:

ALTER INDEX index_name ON table_name DISABLE;

So we can do this, for example:

ALTER INDEX UQ_Employees_Email ON Employees DISABLE;

That disables an index called UQ_Employees_Email on the Employees table.

Disable All Indexes on a Table

We can use the ALL keyword to disable all indexes on the specified table:

ALTER INDEX ALL ON table_name DISABLE;

So in our Employees table, we could do the following to disable all indexes:

ALTER INDEX ALL ON Employees DISABLE;

This will also disable the primary key index. If the table is referenced by any foreign keys, then you will likely get a warning like this:

Warning: Foreign key 'FK_Jobs_Employees' on table 'Jobs' referencing table 'Employees' was disabled as a result of disabling the index 'PK__Employee__7AD04FF1A39ECAB1'.
Warning: Index 'UQ_Employees_Email' on table 'Employees' was disabled as a result of disabling the clustered index on the table.

Here, we can also see that we got a warning that disabling our primary key index (the clustered index) also disabled our other (nonclustered) index. This if fine because that’s what we intended to do in this case.

Important: Disabling a clustered index on a table prevents access to the data. The data still remains in the table, but is unavailable for data manipulation language (DML) operations until the index is dropped or rebuilt.

Also note that the SQL Server documentation states that if a table is in a transactional replication publication, you can’t disable any indexes that are associated with primary key columns.

Verifying Disabled Indexes

To check if an index is disabled, you can query the sys.indexes system view:

SELECT name, is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID('Employees');

If you’re only interested in a single index, then you can include the index’s name:

SELECT name, is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID('Employees')
AND name = 'UQ_Employees_Email';

As mentioned, when we disable the index of a primary key, any foreign keys that reference that primary key will be disabled. Therefore, we may want to check the status of these foreign keys.

We can verify any disabled foreign keys like this:

SELECT 
  OBJECT_NAME(parent_object_id) AS [FK Table],
  name AS [Foreign Key],
  OBJECT_NAME(referenced_object_id) AS [PK Table],
  is_disabled
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('Jobs');

Here we enter the name of the table with the foreign key. That is, the one that references the table that we disabled indexes on.

Re-enabling a Disabled Index

To re-enable a disabled index, use the REBUILD option.

So we can rebuild the indexes on our table like this:

ALTER INDEX ALL ON Employees REBUILD;

Or we can specify a single index:

ALTER INDEX UQ_Employees_Email ON Employees REBUILD;

Bear in mind that we’ll need to re-enable any foreign keys that were disabled when we disabled the primary key index.

In our example we can do this:

ALTER TABLE Jobs
CHECK CONSTRAINT FK_Jobs_Employees;

Why Disable an Index?

Here are some reasons that you might want to disable an index:

  • Faster bulk inserts: Disabled indexes aren’t maintained during data modifications, which can significantly speed up bulk insert operations.
  • Index maintenance: Disabling an index can be useful when you need to rebuild or reorganize it.
  • Troubleshooting: Temporarily disabling an index can help isolate performance issues.
  • Preparation for index removal: You may decide to disable an index that you intend to remove, so that you can monitor things first.

Best Practices and Considerations

  • Be cautious: Disabling indexes can significantly impact query performance. Make sure you understand the implications before disabling an index.
  • Monitor performance: After disabling an index, monitor your queries to ensure they’re still performing as expected.
  • Plan for re-enabling: Remember to re-enable your indexes after you’ve completed your maintenance or bulk insert operations.
  • Consider alternatives: For some scenarios, you might want to consider dropping and recreating the index instead of disabling it.

Index Basics

See Introduction to Indexing in SQL for information about what indexes are and why they’re used.