One of the most common questions on forums is how to disable enable indexes SQL Server. Index can be disabled or enabled as shown below
USE AdventureWorks2014
GO
-- Query1: disable index
ALTER INDEX [IX_Address_StateProvinceID]
ON Person.Address DISABLE
GO
-- Query2: Check index status
SELECT Name,is_disabled from sys.indexes
where object_id=object_id('Person.Address')
The query 1 above disables the index [IX_Address_StateProvinceID] on Person.Address table. The query 2 checks the status of the index. The output of query 2 is shown below.
The index [IX_Address_StateProvinceID] is disabled. To enable an index it needs to be rebuilt as shown below.
ALTER INDEX [IX_Address_StateProvinceID] ON Person.Address REBUILD
Re run the query 2 above to check the index status.
When writing demos for this blog post I ran into an interesting thing. I disabled a clustered index and was unable to select data from the table or perform any other operation.
CREATE table tblone (Sno int identity, col1 int) gO CREATE CLUSTERED INDEX ix_sno on tblone(sno) Go -- disable the clustered index ALTER INDEX ix_sno ON dbo.tblone DISABLE GO select * from dbo.tblone
The above query terminates with below error.
Msg 8655, Level 16, State 1, Line 12
The query processor is unable to produce a plan because the index ‘ix_sno’ on table or view ‘tblone’ is disabled.
Furthermore, disabling primary key clustered index will disable the all foreign keys referencing that primary key. An example of same is shown below
As shown in above snapshot, the SQL server displays warning when disabling primary key clustered that the foreign key referencing tblone is disabled.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
