SQL SERVER – Disable Index – Enable Index – ALTER Index

May 17, 2007

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

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 GO ----Diable Index ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE GO ----Enable Index ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD GO

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

SQL Index, SQL Scripts, SQL Server DBCC

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

SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator

SQL Server Auditing and Recovery With ApexSQL Log 2016

February 9, 2016

SQL SERVER – Stop Growing MSDB Database by Removing sysmail_mailitems History

November 25, 2016

SQL SERVER – Fix: Error: 1934, Level 16, INSERT or UPDATE Failed Because the Following SET Options have Incorrect Settings: ‘QUOTED_IDENTIFIER’.

February 24, 2017

23 Comments. Leave new

Rick
July 18, 2017 1:35 am
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.
Reply
Syam
November 2, 2017 5:35 pm
Sir, Same can be done for clustered index as well?
Reply