Post reply

Disable|Enable Index

  • January 31, 2013 at 2:55 am

    #288570

    Hi Experts,

    I read in a post that during BCP,BULK INSERT kind of operation we can disable the index using

    ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE

    and can re-enable it using

    ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD

    My doubt is whats the difference it makes if we are rebuilding the index at the end? I mean rebuild is just like dropping and recreating index so why cant we drop and recreate instead of disable? Do we have any advantage in disabling?

    Thanks IN Advance

  • January 31, 2013 at 2:58 am

    #1582837

    It is the same you are correct, but with disable / rebuild, you dont need to know the definition of the index to recreate it.

  • January 31, 2013 at 3:03 am

    #1582838

    Thanks Antony for the quick reply.

    Is that the only advantage?

  • January 31, 2013 at 3:06 am

    #1582840

    Yes, as disable drops the B-Tree of the index, but leaves the meta data in place, so the only way to get the index enabled again is rebuild, to rebuild the tree.

    Drop obivously drops the tree and the meta data, so you need to know the name of the index, the columns of the index etc etc in order to re-create it.

  • January 31, 2013 at 3:13 am

    #1582843

    Thanks alot Anthony..

    Those details really helps in understanding exactly what happens..

    Thanks Again

  • January 31, 2013 at 3:38 am

    #1582854

    Just don't try and disable the clustered index. There's a large difference there between dropping and disabling.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 31, 2013 at 6:04 am

    #1582930

    Thanks Gail,

    Can you please help me in understanding the difference? or docs or link is highly appreciated.

  • January 31, 2013 at 6:10 am

    #1582935

    Did you look in Books Online?

    Guidelines for Disabling Indexes and Constraints

    Disabling Clustered Indexes

    The following additional guidelines apply to disabling clustered indexes:

    The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index. This means the following:

    These operations will fail: SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, CREATE STATISTICS, UPDATE STATISTICS (on the index), and ALTER TABLE statements that modify table columns or constraints.

    These operations will succeed: CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE, and DROP TABLE.

    Nonclustered indexes cannot be created while the clustered index is disabled.

    Existing nonclustered indexes and XML indexes associated with the table are automatically disabled and cannot be accessed.

    All clustered and nonclustered indexes on views that reference the table are disabled. These indexes must be rebuilt just as those on the referenced table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 31, 2013 at 7:45 am

    #1583018

    Thanks a lot Gail.

    Seems like disable is pretty dangerous than drop & recreate. 🙂

  • January 31, 2013 at 8:53 am

    #1583047

    No, not at all.

    Just like you wouldn't randomly drop the clustered index (unless you want your table inaccessible for a while and your log to bloat), you wouldn't randomly disable the clustered index.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 4, 2013 at 3:09 am

    #1583865

    Thanks Gail.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply