Disabling Indexes in SQL Server 2005 and SQL Server 2008

By: Ashish Kumar Mehta | Updated: 2009-07-07 | Comments (3) | Related: > Indexing

Problem

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?

Solution

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.

Disabling Indexes

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.

USE AdventureWorks 
GO 
ALTER INDEX IX_Address_StateProvinceID ON Person.Address DISABLE 
GO 

-- Query to check Index Usage 
SELECT NAME AS [IndexName], TYPE_DESC AS [IndexType], 
CASE IS_DISABLED  
WHEN 0 THEN 'Enabled' 
ELSE 'Disabled'  
END AS [IndexUsage],  
FILL_FACTOR AS [FillFactor] FROM SYS.INDEXES 
WHERE OBJECT_ID = OBJECT_ID('Person.Address') ORDER BY IndexName, IndexUsage 
GO

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.

Enabling Indexes

There are different ways by which you can enable indexes. Let's go through each option.

1. Enable Index Using ALTER INDEX REBUILD Statement

USE AdventureWorks 
GO 
ALTER INDEX IX_Address_StateProvinceID ON Person.Address REBUILD 
GO

2. Enable Index Using CREATE INDEX WITH DROP_EXISTING Statement

USE AdventureWorks 
GO 
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID] ON [Person].[Address]  
( 
[StateProvinceID] ASC 
)WITH (DROP_EXISTING = ON, FILLFACTOR = 80) ON [PRIMARY] 
GO

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.

Next Steps

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

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2009-07-07

Comments For This Article

Sunday, September 7, 2014 - 3:32:50 AM - Teach Me SQL SERVER	Back To Top (34424)
http://teachmesqlserver.blogspot.in/2014/08/indexes-in-sql-server_27.html

Thursday, July 12, 2012 - 6:36:25 AM - Adam

Hi Anish.

If I disable an index, truncate the table, load new data into the table and then rebuild the index ... do you know if this is as effective as dropping the index completely, then loading in the data on the truncated table and then rebuilding the index? I am looking at doing this via SSIS

Thanks

Adam

Tuesday, July 7, 2009 - 1:18:57 AM - ALZDBA

This may be overlooked, but as you stated a rebuild of a disabled index will re-enable it !

Hence you need to keep in mind a "rebuild ALL ..." will also re-enable disabled indexes !!

Select object_schema_name( I.[object_id] ) as ObjSchema,object_name( I.[object_id] ) as ObjName , *
from sys.indexes I with (nolock)
where I.is_disabled = 1