SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

How to Disable an Index in SQL Server

In this article we will take a look at how to disable an index in SQL Server. This is a very useful feature which will help you identify whether the index is really useful or not without actually dropping the index.

Advantages of Disabling an Index in SQL Server

Database Administrator can disable an index for a table especially during the times when they are performing performance tuning activities. This feature will help you figure out whether the index present on a table is really useful or not.

The advantage of disabling an index over dropping an index is that when you disable an index for a table the index definition will remain in metadata and at the same time the index statistics are kept ON for non-clustered indexes.

“Disabling a Clustered Index or a Non-Clustered Index on a VIEW will physically delete the Index Data.”

Caveat: If you decide to disable a clustered index on a table then the table will not be available to users but the data will remain intact within the table. However, the table will not be available for any DML operation until you DROP or REBUILT the index.

How to Rebuild Index in SQL Server?

There are different ways in which you can rebuild the index in SQL Server. However the easiest method will be to execute ALTER INDEX REBUILD statement to rebuild an index or execute CREATE INDEX WITH DROP_EXISTING statement to enable a disabled index. In the below demo I will use Sales.Customer table which is available in AdventureWorks2008R2 sample database. For more information see, How to Enable an Index in SQL Server.

Let’s get started and learn in detail different options available to disable an index in SQL Server.

Different Ways to Disable an Index in SQL Server

  • Disable Index Using ALTER INDEX … DISABLE Statement
  • Disable Index Using SQL Server Management Studio (SSMS)

How to Disable an Index in SQL Server Using SSMS?

1. Connect to Database Instance Using SQL Server Management Studio (SSMS)

2. Expand Databases -> Expand AdventureWorks2008R2 database -> Expand Tables -> Expand Sales.Customer table -> Expand Indexes and the right click the index which you want to disable and click Disable. This will open up Disable Index popup.

How to Disable an Index in SQL Server
How to Disable an Index in SQL Server

3. This will open up Disable Indexes popup as shown in the snippet below.

Disable Index Using SSMS
Disable Index Using SSMS

4. Finally, click OK to disable the index.

How to Disable an Index in SQL Server Using TSQL Script?

To disable an index in SQL Server execute the below TSQL script which will disable IX_Customer_TerritoryIDindex of Sales.Customer table available in AdventureWorks2008R2 sample database.

USE AdventureWorks2008R2 
GO

/* Disable Index */
ALTER INDEX [IX_Customer_TerritoryID] ON [Sales].[Customer] DISABLE
GO

How to verify whether index is disabled or not?

Execute the below TSQL script to verify whether the index is disabled or not in SQL Server.

/* verify whether the Index is Disabled or Not? */
SELECT	  name AS [Index Name]
		, type_desc AS [Index Type]
		, index_id AS [Index ID]
		, CASE IS_DISABLED
				WHEN 0 THEN 'Enabled'
				ELSE 'Disabled'
			END AS [Index Usage]
		, FILL_FACTOR AS [Fill Factor]
FROM SYS.INDEXES
WHERE OBJECT_ID = OBJECT_ID('Sales.Customer')
	ORDER BY 2 
GO
How to Check whether the index is disabled or not in SQL Server
How to Check whether the index is disabled or not in SQL Server

Conclusion

Disabling an index in SQL Server feature will help you figure out whether the index present on a table is really useful or not.

Trending Articles

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips