SQL SERVER – How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’

Many times I have seen that the index is disabled when there is large update operation on the table. Bulk insert of very large file updates in any table using SSIS is usually preceded by disabling the index and followed by enabling the index. I have seen many developers running the following query to disable the index.

USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO

While enabling the same index, I have seen developers using the following INCORRECT syntax, which results in error.

USE AdventureWorks
GO
----INCORRECT Syntax Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact ENABLE
GO

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘ENABLE’.

This is because once the index is disabled, it cannot be enabled, but it must be rebuilt. The following syntax will enable and rebuild the index with optimal performance.

USE AdventureWorks
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO

I hope that now you have understood why enabling this syntax in the index throws an error and also how to enable an index with optimal performance.

If you want to read just the syntax, I have written a similar article earlier SQL SERVER – Disable Index – Enable Index – ALTER Index.

Reference: Pinal Dave (https://blog.sqlauthority.com)