Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Disabling Indexes in SQL Server 2005 and SQL Server 2008


By:   |   Read Comments (3)   |   Related Tips: More > 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 THEN 'Enabled'
ELSE 'Disabled' 
END AS [IndexUsage]
FILL_FACTOR AS [FillFactor] FROM SYS.INDEXES
WHERE OBJECT_ID OBJECT_ID('Person.Address'ORDER BY IndexNameIndexUsage
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 = ONFILLFACTOR = 80ON [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



Last Update:






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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Sunday, September 07, 2014 - 3:32:50 AM - Teach Me SQL SERVER Back To Top

http://teachmesqlserver.blogspot.in/2014/08/indexes-in-sql-server_27.html


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

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 07, 2009 - 1:18:57 AM - ALZDBA Back To Top

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

 


Learn more about SQL Server tools