SQL SERVER – Disable Index – Enable Index – ALTER Index

There are few requirements in real world when Index on table needs to be disabled and re-enabled afterwards. e.g. DTS, BCP, BULK INSERT etc. Index can be dropped and recreated. I prefer to disable the Index if I am going to re-enable it again.

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

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

SQL Index, SQL Scripts, SQL Server DBCC
Previous Post
SQL SERVER – Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction
Next Post
SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator

Related Posts

23 Comments. Leave new

  • Is there any way that we can drop the index without removing Foreign key?

    ERROR Message :
    Msg 3723, Level 16, State 6, Line 2
    An explicit DROP INDEX is not allowed on index ‘Index_name’. It is being used for FOREIGN KEY constraint enforcement.

    I know we can drop the foreign key and then re-create it but I’m dropping 1000s of Indexes and then re-create them all.
    So, I just want to make sure before doing it.

  • Sir, Same can be done for clustered index as well?


Leave a Reply