Disable an index in SQL Server

How to disable an index in SQL Server? To disable an index for performance reasons, use this code and adjust it to your index and table names.

For instance, let’s consider a large sales table, with million lines. Let’s consider that the table has a non-unique and non-clustered index on the month.

Query to disable an index on a SQL Server table

First, why it is better to disable an index on a given table?

In other words, to insert data faster in the table, it’s much better to disable this index. For instance, let’s use the same index from this database tutorial on how to create a SQL Server index, and then disable it.

The goal is to explicitly disable the index usage on the table by the Relational Database Management System (RDMS).

ALTER INDEX indexYearMonth 
	ON [dbo].[SALES] DISABLE;

After been disabled, the index is not used anymore, and it need to be rebuilt to be enabled again.

To sum up, more insights on disabling the indexes and SQL constraints in the official MS database design documentation.

In order to check all indexes on a database, it possible to use the SQL query from this IT tutorial on how list all SQL Server indexes from a database.

List indexes in SQL Server database with a query

To finish, this IT tutorial presents a useful T-SQL command to disable an index on the sales table in a SQL Server database.