Facts About Enabling / Disabling Indexes in Sql Server

If you are in performance tuning, you must have encounter enabling and disabling multiple indexes . There are couple of facts one should not forget else

Syntax : How To Disable - Enable Indexes In SQL Server

1. The index does not entertain any maintenance while it is disabled.
2. The query optimizer does not consider the disabled index when creating query execution plans.
3. The queries that reference the disabled index with a table hint fail.
4. You cannot create an index that uses the same name as an existing disabled index.
5. Disabling index also disable reference ( PK / FK ) key.
6. Non clustered indexes are automatically disabled when the associated clustered index is disabled
7. Non clustered indexes must be explicitly enabled, unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement.
8. Non Clustered index cannot be enabled until either the clustered index on the table or view is enabled or dropped.
9. The ALTER INDEX ALL on table REBUILD statement doesn't rebuilds disabled indexes on views.
10. You can rebuild a disabled non clustered index online when the table does not have a disabled clustered index.
11. You must always rebuild a disabled clustered index offline.
12. The CREATE STATISTICS statement cannot be successfully executed on a table that has a disabled clustered index.
13. If a clustered index is disabled, DBCC CHECKDB cannot return information about the underlying table
14. DBCC INDEXDEFRAG cannot be used to defragment a disabled index.
15. Creating a new clustered index enables previously disabled non clustered indexes.
16. A disabled index can be dropped.
17. After rebuilding \ Enabling the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.
18. Indexes on views must be enabled in a separate ALTER INDEX ALL on view REBUILD statement.
19. Enabling index means indexes must be rebuilt just as those on the referenced table.