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)

, ,
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

  • If i disable an index and import a load of data, will the indexes:
    – re-index when i re-enable the index
    – re-index as the data is importing even though they’re disabled
    – or do they not reindex until a dbreindex / alter table command is submitted



  • Jignesh Thanki
    November 7, 2007 5:06 pm


    won’t work with SQL 2000

  • Pls, help me!

    I tried to convert all CHAR columns of a DB to VARCHAR in MS SQL-2000.

    But because of the dependece of indexes my procedure fails.
    If I drop the idx-es I don’t know how to create them back with all the previous atributes.

    How can I just disable the idx-es and afterwords enable them?
    If Iknow well, ALTER INDEX…DISABLE exists only in MS SQL-2005.


  • Hi Dave,

    How to disable the index in SQL 2000?I tried alter index,but it didn’t work.Please help me on this matter.

    I would like to know that what is the difference between index and nonclustered index?


  • Hi ,
    I want to disable a Primary Key Clustered Index made on five columns :
    CREATE CLUSTERED INDEX PK_ArrangementMeasure ArrangementMeasure(ArrangementOID, ArrangementMeasureTypeOID, UnitOfMeasureOID,MeasurementEventOID,SourceSystemImplementationOID)
    before a target table update/insert of 24 lakh/2 million records. Beacuse your command is useful for disabling index on 1 column how to do it for more thn one column…Early reply will be appreciated. Thank u.Please mail soln at my yahoo id ankur_argal@yahoo.com

  • Imran Mohammed
    August 29, 2008 8:24 pm

    @ Ankur,

    I may be wrong but this is what I think.

    The script just talks about index and on which table, no where it discuss columns.

    You are talking about composite primary keys. One primary key will have only one clustered indexes no matter if it has one column or more than one column.

    I dont think the script given by Pinal dave will change for a single column primary key or composite Primary key ( more than one column).

    It makes sense to me, because no where in the script we mentioned column name, only two names are mentioned.,

    1. one is index name
    2. other is table name ( owner.table_name).

    Hope this helps.

  • This is helpful. However , I believe that rebuilding an index willl reorganize the data again (with new inserts). Suppose if I have 10 crores of records. Will it take too long to enable the index. I have tested it and it takes about 3 hours for 10 crores of records.
    Also, if I do that it creates a huge log file , which takes a lot of space on server.
    Is there a way to deal with this?

    Is that


  • I am using Microsoft SQL server 2008.
    I am having trouble with this enable/disable option.
    I disabled clustered index that I created and then try to insert some data and got the error message.

    The query processor is unable to produce a plane because “index_name” on “table_name” is disabled.

    Does anybody has alternative for SQL server 2008?

    Thanks for help in advance.

  • “Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt.”

    From what I understand this means that when you disable the clustered index, the table becomes read-only.

    It isn’t very clear though: it says the disabled clustered index must be dropped or rebuilt in order for the data to become update able again.

    so, you need to drop the index to proceed.

    Disabling a non-clustered index or clustered index on a view physically deletes the index data

  • I have table which has 16 lakhs records

    i have to retrive the record below 4 seconds

    now it is taking 20 seconds

    i have created an index also for the id which has the primary key still this problem is there.

    what should i have to do

    please help me

  • I want to modify a column that has an index on it (2005 & 2008). I really don’t want to drop the index but disabling it doesn’t seem to be helping. Is there a way to alter a column without dropping the index?

  • “Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt.”

    The clustered index determines in which order data is organized in the table’s pages. Obviously, if you disable that index, no insert/delete operations can be possible, and all other indexes refer to bookmarks within the clustered indes.

    Only when you drop the clustered index, the table becomes a pretty much unorganized heap, and all indexes are rebuilt to not refer to the clustered index anymore.

  • Raul Alejandro Vejar Llugany
    March 23, 2010 1:17 am


    Very useful for bulk imports / updates

  • hey after disabling the index of that table the table is not accessible

  • Khwaza Bandenawaz
    December 1, 2011 3:28 pm

    Hi Pinal,

    Suppose we disabled all the indexes on all the tables than how we can identify that the all the indexes has disabled?

    • Sudhanshu K Guru
      November 3, 2012 2:02 am

      You can try generating the create script for the indexes, you will see the last line says.. alter and disable. It means, the Index is disabled. ~Thnx

  • how to use drop_existing option

  • I need to Alter or change all the index properties in my DB. The index have 4 columns in asc order I need one of the columns to be in desc order. Would you know the syntax or easy method on doing this?
    To change index properties

  • Hi,

    am trying to load 1billon records through SSIS. and the data has to be deleted and again new data has to be inserted from another server. its taking more than 1 hr for me to do so. can you please help me out here…

  • Pavani, this isn’t a question for indexing. Loading data is not the purpose of an index. If you are using SSIS then you should become familiar with the multi-cast tool and use that in a plan where you create several insertion points for the data based on how much each record requires plus the memory requirements of your overhead. The key is in figuring out how much data you can load in without creating memory pressure that spills over onto disc-cache.

    1 Billion records is quite a lot of data no matter how you form it – network connections and physical architecture have an impact too.

  • Can i do all DML operartions after disabling indexes?


Leave a Reply