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.
Big Data and SQL 
One comment