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
- The index does not entertain any maintenance while it is disabled.
- The query optimizer does not consider the disabled index when creating query execution plans.
- The queries that reference the disabled index with a table hint fail.
- You cannot create an index that uses the same name as an existing disabled index.
- Disabling index also disable reference ( PK / FK ) key.
- Non clustered indexes are automatically disabled when the associated clustered index is disabled
- Non clustered indexes must be explicitly enabled, unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement.
- Non Clustered index cannot be enabled until either the clustered index on the table or view is enabled or dropped.
- The ALTER INDEX ALL on table REBUILD statement doesn't rebuilds disabled indexes on views.
- You can rebuild a disabled non clustered index online when the table does not have a disabled clustered index.
- You must always rebuild a disabled clustered index offline.
- The CREATE STATISTICS statement cannot be successfully executed on a table that has a disabled clustered index.
- If a clustered index is disabled, DBCC CHECKDB cannot return information about the underlying table
- DBCC INDEXDEFRAG cannot be used to defragment a disabled index.
- Creating a new clustered index enables previously disabled non clustered indexes.
- A disabled index can be dropped.
- After rebuilding \ Enabling the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.
- Indexes on views must be enabled in a separate ALTER INDEX ALL on view REBUILD statement.
- Enabling index means indexes must be rebuilt just as those on the referenced table.
No comments:
Write commentsPlease do not enter spam links