MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter












































Latest SQL Server Tips

Different Ways to Enable an Index in SQL Server

Read Comments   |   Related Topics: SQL Server Indexing Articles & Tips > Next Topic: Database Backup & Restore Tips


How to Enable an Index in SQL Server?

In this article we will take a look at how to enable an index in SQL Server. This is a very useful feature which will help you enable an index which was disabled earlier to check whether the index was really useful or not without actually dropping the index. This feature is available in SQL Server 2005 and higher version.

How to verify whether an index is disabled or not in SQL Server?

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 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 Disable an Index in SQL Server.

Different Ways to Enable an Index in SQL Server

  • Enable an Index Using ALTER INDEX...REBUILD Statement
  • Enable an Index Using CREATE INDEX WITH DROP_EXISTING Statement
  • Enable an Index in SQL Server Using SQL Server Management Studio (SSMS)

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

Become a Database Expert: Update your Database Backup and Restore Skills. For more information, see SQL Server Database Backup Tutorial with Examples.

How to Enable 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 enable and click Rebuild.

How to Rebuild an Index in SQL Server

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

Rebuild Index in SQL Server Using SSMS

4. Finally, click OK to Rebuild the index which was disable before.

How to Enable an Index Using ALTER INDEX...REBUILD Statement?

To enable an index in SQL Server execute the below mentioned TSQL script which will enable IX_Customer_TerritoryID index of Sales.Customer table which is available in AdventureWorks2008R2 sample database.

USE [AdventureWorks2008R2]
GO

ALTER INDEX [IX_Customer_TerritoryID] ON [Sales].[Customer] REBUILD 
GO

How to Enable an Index in SQL Server Using CREATE INDEX WITH DROP_EXISTING Statement?

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

USE [AdventureWorks2008R2] 
GO 

CREATE NONCLUSTERED INDEX [IX_Customer_TerritoryID] ON [Sales].[Customer]
(
[TerritoryID] ASC
)
WITH (DROP_EXISTING = ON,
FILLFACTOR = 90) 
GO

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server

Learn More...







Last Updated On: June 18, 2015



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: