Disable or rebuild all nonclustered indexes on a table

  • Jonathan AC Roberts

    SSCoach

    Points: 16992

    Comments posted to this topic are about the item Disable or rebuild all nonclustered indexes on a table

  • Jeff Moden

    SSC Guru

    Points: 995102

    Just a word of caution here... the script is fine and the reason for disabling indexes prior to rebuilds or imports is great but be very aware that if you disable an index that an FK is pointing to, it will disable that FK and you'll need to rebuild the FK.  Here's the skinny from BOL on the subject...

    When disabling a unique index, the PRIMARY KEY or UNIQUE constraint and all FOREIGN KEY constraints that reference the indexed columns from other tables are also disabled. When disabling a clustered index, all incoming and outgoing FOREIGN KEY constraints on the underlying table are also disabled. The constraint names are listed in a warning message when the index is disabled. After rebuilding the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.

    To be sure Jonathon mentioned that this would be used "when inserting data into a staging table on  a data warehouse", which also means that there are probably no Foreign Keys pointing at the table in question.  I just want to make sure that people don't do such a thing to real tables during inserts or index maintenance without knowing that there are other serious ramifications to be had.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 2 (of 2 total)

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