Disable Indexes and Constraints in SQL Server

We know that Indexes can help queries to find data quickly in a database in SQL Server where they provide opportunities for much more efficient use of I/O because of having great potential. Downside to using an index is the performance implication on data modification statements means any time a query modifies the data in a table (INSERT, UPDATE, or DELETE) then the database needs to update all of the indexes where data has changed.

After an index is disabled in SQL Server, it remains in a disabled state until it is rebuilt or dropped.

To improve the other operations (INSERT, UPDATE, or DELETE) performance on the data table, it becomes an urgent requirement to disable or drop indexes on that table. If we are going to disable any index then we should be awarded of the following limitations and restrictions of this process –

• SQL Server does not maintain a disabled index as well as the SQL Server Query Optimizer does not consider any disabled index in the query execution plans.
• If any existing queries that reference these disabled indexes with a table hint must be failed.
• SQL Server does not allow us to create an index that uses the same name as an existing disabled index and will not be allowed to drop any disabled index.
• After disabling a unique index, all constraints such as PRIMARY KEY or UNIQUE KEY constraint and all FOREIGN KEY constraints that reference that indexed columns from other tables are also disabled.
• After disabling a clustered index, all incoming and outgoing FOREIGN KEY constraints on the underlying table are also disabled. 

Note: The constraint names are listed in a warning message when the index is disabled. After rebuilding the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement. The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index.

• All depended nonclustered indexes are automatically disabled when the associated clustered index is disabled and cannot be enabled until either the clustered index on the table or view is enabled or the clustered index on the table is dropped.
• Nonclustered indexes must be explicitly enabled, unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement.
• After disabling a clustered index on a table, SQL Server automatically disables all clustered and nonclustered indexes on views that reference that table.
• SQL Server helps us to rebuild a disabled nonclustered index online when the table does not have a disabled clustered index. Though, we must always rebuild a disabled clustered index offline if we use either the ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING statement.
• SQL Server does not allow to CREATE STATISTICS statement on a table that has a disabled clustered index. In this case, AUTO_CREATE_STATISTICS database option creates new statistics on a column when the index is disabled.

How to disable index on a table?

Before disabling any index, you must have at least ALTER INDEX permission. There are two ways to disable index in SQL Server such as through SQL Server Management Studio and T-SQL statements-

By using SQL Server Management Studio, we need to expend our index folders of the data table in the database as given below-

After expending the index folder, right click on the index and choose Disable. It will be launched Disable Indexes window where we need to click on OK button as given below-

By using Transact-SQL - In this scenario, we have to disable a particular index or all indexes on a table as given below-  

USE DEMO;  GO  -- Disables IndCustItems index  -- on the dbo.TB_SalesOrders table  ALTER INDEX IndCustItems ON dbo.TB_SalesOrders DISABLE;  -- Disables all indexes on dbo.TB_SalesOrders table ALTER INDEX ALL ON dbo.TB_SalesOrders DISABLE;

Conclusion

After disabling the index, it definition remains in metadata, and index statistics are kept on nonclustered indexes and after rebuilding the index, any constraints that were disabled because of disabling the index must be manually enabled.  Disabling a nonclustered or clustered index on a view physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for data manipulation language (DML) operations until the index is dropped or rebuilt.
How to enableIndexes and Constraints in SQL Server

Reference:https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints