Disable and Enable Index in SQL Server

This topic describes how to disable and enable an index in SQL Server. Disabling an index prevents users from accessing the index and, in the case of clustered indexes, the underlying table data. On nonclustered indexes, the index definition is kept in metadata, and index statistics are kept. Disabling a clustered index on a view or a nonclustered index deletes the index data physically. Disabling a clustered index on a table prevents access to the data; the data remains in the table but is unavailable for DML operations until the index is dropped or rebuilt.

USE <DatabaseName>
GO
ALTER INDEX Index_Name ON Table_Name DISABLE;
GO

Use the following command to enable a disabled index (REBUILD enables a disabled index)

USE <DatabaseName>
GO
ALTER INDEX Index_Name ON Table_Name REBUILD;
GO

Please keep in mind that the ALTER INDEX command does not include an ENABLE argument.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s