Disabling Indexes in SQL Server 2005 and SQL Server 2008
While looking through the new features in SQL Server 2005 and SQL Server 2008 we found a potentially interesting one called Disabling Indexes, which can be used to disable indexes on a table or a view. Can you give us a detailed explanation of how we go about using this new feature along with examples?
Database Administrators can use the Disabling Indexes feature which is available in SQL Server 2005 and later versions to prevent the index usage by user queries. This feature is very useful for DBA's when they need to figure out whether the indexes which are available on a table are really useful or not. When you are disabling an index the index definition remains in metadata and index statistics are also kept on non-clustered indexes. However, disabling a clustered index or a non-clustered index on a view physically deletes the index data.
If you are disabling a clustered index on a table then the table won't be available for user access, however the data will still remain in the table, but it will be unavailable for any DML operation until the index is rebuilt or dropped. You can use ALTER INDEX REBUILD to rebuild an index and CREATE INDEX WITH DROP_EXISTING statement to enable a disabled index.
In this tip I will be using the Person.Address table which is available in the AdventureWorks database.
There are different ways by which you can disable an index. Lets us go by each option one by one.
1. Disabling Index Using T-SQL
Execute the below T-SQL to disable IX_Address_StateProvinceID index which is available on Person.Address table of AdventureWorks database.
2. Disabling Index Using SQL Server Management Studio (SSMS)
Expand Object Explorer for the AdventureWorks Tables until you get to Person.Address and then expand Indexes and right click IX_Address_StateProvinceID (Non-Unique, Non-Clustered) and select Disable from the drop down list. This will open up Disable Indexes dialog box, click OK to disable the index.
You can also disable an index by unchecking Use Index option as shown in the below snippet which is available in the Options page for the Index Properties dialog box.
There are different ways by which you can enable indexes. Let's go through each option.
1. Enable Index Using ALTER INDEX REBUILD Statement
2. Enable Index Using CREATE INDEX WITH DROP_EXISTING Statement
3. Enable Index Using SQL Server Management Studio (SSMS)
Expand Object Explorer for the AdventureWorks Tables until you get to Person.Address and then expand Indexes and right click IX_Address_StateProvinceID (Non-Unique, Non-Clustered) and select Rebuild from the drop down list. This will open up Rebuild Indexes dialog box, click OK to enable the index.
You can also enable an index by selecting Use Index option as shown in the below snippet which is available in the Options page for the Index Properties dialog box.
- To view a list of unused indexes take a look at this tip: Deeper insight into unused indexes for SQL Server
- Here are some additional tips about indexing
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips