How To Disable - Enable Indexes In SQL Server

                Indexes can be very useful for select queries but large no. of indexes can made DML operation performance pathetic. And if  we decide or get recommendation to drop these indexes , The best practice says to disable it first and if every thing goes good go ahead and drop index

 /*******************SQL Syntax************************/

USE [DB_name]
GO
----Disable Index
ALTER INDEX [Index_Name] ON [Table_Name] DISABLE
GO
---- Disable All Indexes
ALTER INDEX ALL ON [Table_Name] DISABLE
GO
 ----Enable Index
ALTER INDEX [Index_Name] ON [Table_Name] REBUILD
GO
----Enable All Index
ALTER INDEX ALL ON [Table_Name] REBUILD
GO

Before disabling index we should not for get below:

• Disabling a non clustered index prevents user access to the index.
• Disabling clustered indexes on table doesn't delete data but prevents user access to underlying table data.
• Disabling a non clustered or clustered index on a view physically deletes the index data.
• Enabling index means you have to rebuild it, There is no keyword called as enable.

Must Refer: Facts about Enabling / Disabling Indexes: