SQL Server Enable Indexes

Summary: in this tutorial, you will learn how to use various statements to enable one or all disabled indexes on a table.

Sometimes, you need to disable an index before doing a large update on a table. By disabling the index, you can speed up the update process by avoiding the index writing overhead.

After completing the update to the table, you need to enable the index. Since the index was disabled, you can rebuild the index but cannot just simply enable it. Because after the update operation, the index needs to be rebuilt to reflect the new data in the table.

In SQL Server, you can rebuild an index by using the ALTER INDEX statement or DBCC DBREINDEX command.

Enable index using ALTER INDEX and CREATE INDEX statements

This statement uses the ALTER INDEX statement to “enable” or rebuild an index on a table:

ALTER INDEX index_name 
ON table_name  
REBUILD;
Code language: SQL (Structured Query Language) (sql)

This statement uses the CREATE INDEX statement to enable the disabled index and recreate it:

CREATE INDEX index_name 
ON table_name(column_list)
WITH(DROP_EXISTING=ON)
Code language: SQL (Structured Query Language) (sql)

The following statement uses the ALTER INDEX statement to enable all disabled indexes on a table:

ALTER INDEX ALL ON table_name
REBUILD;
Code language: SQL (Structured Query Language) (sql)

Enable indexes using DBCC DBREINDEX statement

This statement uses the DBCC DBREINDEX to enable an index on a table:

DBCC DBREINDEX (table_name, index_name);
Code language: SQL (Structured Query Language) (sql)

This statement uses the DBCC DBREINDEX to enable all indexes on a table:

DBCC DBREINDEX (table_name, " ");  
Code language: SQL (Structured Query Language) (sql)

Enable indexes example

The following example uses the ALTER INDEX statement to enable all indexes on the sales.customers table from the sample database:

ALTER INDEX ALL ON sales.customers
REBUILD;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned various statements including ALTER INDEX, CREATE INDEX, and DBCC DBREINDEX to enable one or all indexes on a table.