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.
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
Reference : Pinal Dave (https://blog.sqlauthority.com)
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?