How to disable indexes in SQL Server 2005 and 2008?

This entry was posted in Sql Server 2005 Sql Server 2008 Sql Server DBA Sql Server tutorials and tagged disable indexes sqlserver2005 sqlserver2008 on by .

Scenario proposal

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

1

 

  1. 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.

2

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.

3

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.

4

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.

 5

Thanks for reading this article,

Next steps :

  1. Add this article to your database toolkit
  2. Share this with your colleagues because Sharing is Learning
  3. Comment below if you need any assistance