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

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

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

Stored Procedure USP_DisableEnableNonClusteredIndexes can be downloaded from here.

Execute SP using following script.

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

Here are few additional blog posts related to index, which you should read about:

Introduction to Force Index Query Hints – Index Hint

This article, I will start with disclaimer instead of having it at the end of the article.

“SQL Server query optimizer selects the best execution plan for a query, it is recommended to use query hints by experienced developers and database administrators in case of special circumstances.”

SQL SERVER – Introduction to Force Index Query Hints – Index Hint – Part2

I just received an email from one of my regular readers that, are there any other methods for the same as it will be difficult to read the syntax of the joint.Yes, there is an alternate way to do the same using OPTION clause, however, as an OPTION clause is specified at the end of the query we have to specify which table the index hint is put on.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQLAuthority Author Visit – A True Outsourcing Giant and Technology Leader DigiCorp in Ahmedabad India
Next Post
SQLAuthority News – Ahmedabad User Group Meeting February 21 2009

Related Posts

13 Comments. Leave new

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

    Reply
  • Reply
  • @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.

    Reply
  • good

    Reply
  • it’s not working on my adventureworks database
    when i try to use is says invalid object name sys.databases

    Reply
  • 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

    Reply
  • Amarjeet Singh
    May 28, 2010 11:31 am

    Its Awesome.

    Reply
  • 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

    Reply
  • 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

    Reply
    • This selects what you need

      select clnum, linenum ,status ,max(date) as date from table
      group by clnum, linenum ,status

      Reply
  • 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.

    Reply
  • Thanks Pinal, helpful as always. I ran into 2 issues with your stored proc in my environment. First, to exclude indexes on system tables (for replication) I added another condition to the WHERE in the dynamic SQL – AND T.is_ms_shipped = 0. Second, my database has tables with the same name but in different schemas, so I had to add an additional condition to the JOIN to Information_Schema.Tables – AND SC.TABLE_SCHEMA = SCHEMA_NAME(T.Schema_ID). The final version works as intended. Here is the full updated version of the SELECT.

    SET @SQL1 = ‘USE ‘+@DB_NAME+ ‘
    SELECT IDENT = IDENTITY (INT,1,1)
    ,SC.[TABLE_SCHEMA]+”.”+SC.[TABLE_NAME] [FULLOBJECTNAME]
    ,SI.[NAME] [INDEXNAME]
    INTO ##STOREINDEXINFORMATION
    FROM SYS.INDEXES I JOIN SYS.TABLES T ON I.[OBJECT_ID] = T.[OBJECT_ID] JOIN SYSINDEXES SI ON SI.ID = T.[OBJECT_ID] JOIN INFORMATION_SCHEMA.TABLES SC ON SC.TABLE_NAME = OBJECT_NAME (T.[OBJECT_ID]) AND SC.TABLE_SCHEMA = SCHEMA_NAME(T.Schema_ID)
    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 T.is_ms_shipped = 0’

    Reply
    • This is excellent. I will update the original blog post with due credit to you and also will create a new blog post where I link to the blog post.

      Many thanks,

      Reply

Leave a Reply

Menu