SQL SERVER – Enable and Disable Index Non Clustered Indexes Using T-SQL

Just a day before I received wonderful T-SQL Script from SQL Server Expert Imran Mohammed. What I am really impressed from this script is additional feature of changing recovery mode when SQL Server is enabling and disabling Index. When index are enabled changing SQL Server Recovery mode to simple makes whole operation faster. This stored procedure is self documented so I am not writing much about it.

I really want to encourage all blog readers and SQL Expert to actively participate like Imran Mohammed.

Stored Procedure USP_DisableEnableNonClusteredIndexes can be downloaded from here.

Execute SP using following script.

-- sample to Execute
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'N',1 -- DISABLE
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'N',2 -- REBUILD/ENABLE with out Changing Recovery model.
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'Y',2 -- REBUILD/ENABLE with Change Recovery model.
-- or
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS',NULL, 1 -- DISABLE
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', NULL ,2 -- REBUILD/ENABLE with out Changing Recovery model.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

12 thoughts on “SQL SERVER – Enable and Disable Index Non Clustered Indexes Using T-SQL

  1. What would be the ramifications of changing the recovery model of your database?
    You would have a service level agreement with the business on a recovery strategy and changing your recovery model during maintenance tasks probably is not part of this agreement. Plus, how do you recover if the database goes down after you have switched the recovery model back and forth from full or bulk logged to simple and then back again?
    Are you taking a full backup once the database is in simple mode – or what exactly would be your suggested approach of keeping integrity within the database and backups of the database in sync with what you are doing as well as keeping to the contract of your SLA?
    I see this as a fun thing to do on a database that has no value such as adventureworks, but on a production database – processing many transactions per minute – with a service level agreement in place on a recovery strategy this is not going to sit well with the business that is paying for your services.

  2. @Simon Worth

    I appreciate your response for the above article. I am not a genius. I love when experts like you comment on my work. I thank you for the same.

    I will not defend myself, but would like to bring two points to your notice,

    1. Script by default will not change recovery model, unless user specify an option to change it. It is just an added option.

    2. This is only for Non-Clustered Index (excluding one created on Primary Keys and Unique Keys). Script was created to reduce execution time for huge data loads.

    @Jason Crider

    Thanks for comment, I completely agree with you.

    I could have added Fill Factor as a parameter when rebuilding indexes, I could have used case function and removed many IF statements.

    Regards,
    IM.

  3. Hello Emeka,

    Please make sure that you have required permission to see the result of sys.databases catalog view. According to BOL:

    “If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database.”

    Regards,
    Pinal Dave

  4. Pingback: SQL SERVER – Disable Clustered Index and Data Insert Journey to SQL Authority with Pinal Dave

  5. Hello,
    great script. Just one question:

    Inside script, why don’t check the value on “is_unique” field in sys.indexes too?

    …WHERE I.[INDEX_ID] > 1
    AND I.[TYPE] = 2
    AND I.[IS_PRIMARY_KEY] 1
    AND I.[IS_UNIQUE_CONSTRAINT] 1
    AND I.[INDEX_ID] = SI.INDID’

    Should be:
    …WHERE I.[INDEX_ID] > 1
    AND I.[TYPE] = 2
    AND I.[IS_PRIMARY_KEY] 1
    AND I.[IS_UNIQUE_CONSTRAINT] 1
    AND I.[INDEX_ID] = SI.INDID’
    AND I.[IS_UNIQUE] 1

    TIA
    David

  6. Hi all

    Please solve my prob anybody it is urgent
    here i am giving some data. i want to delete duplicate records from table
    these are all duplicate records with clnum,linenum,status from this data i have to consider only latest “date” records and “id” column is also unique and i have to delete old dated records.

    clnum linenum status date id
    1 3 11 2011-02-24 9092
    1 3 11 2011-03-22 16235
    2 4 11 2011-03-22 16290
    2 4 11 2011-02-24 11572
    3 2 11 2011-03-22 16222
    2 4 11 2011-02-24 11572

    for ex from the above data i have to consider only

    clnum linenum status date id
    1 3 11 2011-03-22 16235
    2 4 11 2011-03-22 16290
    3 2 11 2011-03-22 16222

    and i have to delete

    clnum linenum status date id
    1 3 11 2011-02-24 9092
    2 4 11 2011-02-24 11572
    2 4 11 2011-02-24 11572

    Please help me it’s urgent

  7. Hi Pinal.

    This is a great script, but is there a revised version that includes tablename so that only the non-clustered indexes on a specific table are disabled/rebuilt?

    Thanks.

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