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
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 (http://blog.SQLAuthority.com)

About these ads

24 thoughts on “SQL SERVER – Disable Index – Enable Index – ALTER Index

  1. 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

    ??

    thanks

    Like

  2. 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.

    Thanks!

    Like

  3. 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?

    Thanks

    Like

  4. 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

    Like

  5. @ 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.
    Imran.

    Like

  6. 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

    -Manish

    Like

  7. 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.
    -Manish

    Like

  8. “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

    Like

  9. 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

    Like

  10. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  11. 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?

    Like

  12. “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.

    Like

  13. Pingback: SQL SERVER – How to Enable Index – How to Disable Index – Incorrect syntax near 'ENABLE' Journey to SQL Authority with Pinal Dave

  14. Hi Pinal,

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

    Like

    • 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

      Like

  15. 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

    Like

  16. 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…

    Like

  17. 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.

    Like

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s