Enable and Disable Index in Microsoft SQL Server

Check the Status enable/disable of Index in Microsoft SQL Server

Check the Status of indexes in Microsoft SQL Server:

SELECT  name AS [Index Name], type_desc AS [Index Type]
        , CASE IS_DISABLED WHEN 0 THEN 'Enabled' ELSE 'Disabled' END AS [Index Status],index_id AS [Index ID]
       FROM SYS.INDEXES
WHERE OBJECT_ID = OBJECT_ID('TABLE_NAME') and name = 'INDEX_NAME'

Example:
SELECT  name AS [Index Name], type_desc AS [Index Type]
        , CASE IS_DISABLED WHEN 0 THEN 'Enabled' ELSE 'Disabled' END AS [Index Status],index_id AS [Index ID]
       FROM SYS.INDEXES
WHERE OBJECT_ID = OBJECT_ID('[HumanResources].[Employee]') and name = 'hremployeejogtitile_idx';

Output:
Index Type	 Index Name	             Index Status Index ID
NONCLUSTERED hremployeejogtitile_idx Enabled      7

Disable the index in Microsoft SQL Server

Command: 
ALTER INDEX index_name on schema_name.table_name DISABLE;
Example:
ALTER index hremployeejogtitile_idx on HumanResources.Employee DISABLE;

Disable with GUI:

Enable the Index in Microsoft SQL Server:

Command:
ALTER INDEX index_name ON schema_name.table_name REBUILD;  
Example:
ALTER INDEX hremployeejogtitile_idx ON HumanResources.Employee REBUILD;

Enable with GUI:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.