- Disabling a clustered index on a view will DELETE the data. ouch!
- Disable a clustered index, the data is still present, but it cannot be used. This is because the leaf level of the clustered index is the actual table data itself.
- Disable a non-clustered index, the index actually remains on disk, and there is no physical disk change at all.
- A primary key cannot be disabled if the associated table is published within transactional replication. (Makes sense.)
- To ENABLE the index, it has to be rebuilt. This is because when we DISABLE an index, the definition remains in the meta data, but the index updates are not performed. It has to be rebuilt, and brought current, before it can be enabled.
Indexes can be disabled and enabled within the SSMS gui, but this is the tSQL method:
ALTER INDEX IndexName ON dbo.TableName DISABLE;
ENABLE Index:
ALTER INDEX IndexName ON dbo.TableName REBUILD;
OR
CREATE NONCLUSTERED INDEX IndexName ON dbo.TableName
(IndexKeyName ASC)
WITH (DROP_EXISTING = ON, FILLFACTOR = 80) ON [PRIMARY];
This is just a quick reference, but I encourage you to also read these guidelines:
Guidelines for Disabling Indexes and Constraints
No comments:
Post a Comment