SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact

While playing with Indexes I have found following interesting fact. I did some necessary tests to verify that it is true.

When a clustered index is disabled, all the nonclustered indexes on the same tables are auto disabled as well. User do not need to disable non-clustered index separately. However, when clustered index is enabled, it does not automatically enable nonclustered index. All the nonclustered indexes needs to be enabled individually. I wondered if there is any short cut to enable all the indexes together. Index rebuilding came to my mind instantly. I ran T-SQL command of rebuilding all the indexes and it enabled all the indexes on table.

This was very intriguing to me, as I never faced this kind of situation before. Everyday there is something new in SQL Server and that what makes life of DBA refreshing.

T-SQL to rebuild all the indexes on table.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Rebuild All Indexes using SQL Server Management Studio.

Expand AdventureWorks Database, Expand Production.Product Database, Expand Index Group

SQL SERVER - Enabling Clustered and Non-Clustered Indexes - Interesting Fact indexrebuild1

Right click on Indexes and select Rebuild All

SQL SERVER - Enabling Clustered and Non-Clustered Indexes - Interesting Fact indexrebuild2

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

SQL Index, SQL Scripts
Previous Post
SQL SERVER – DISTINCT Keyword Usage and Common Discussion
Next Post
SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

Related Posts

10 Comments. Leave new

  • Gr8 piece! Keep it up.

    Reply
  • Hi Pinal,

    To enable all the indexes together, run the Alter Index All command. eg

    “ALTER INDEX ALL ON Production.Product REBUILD”
    The above command will rebuild and enable all indexes that has been disabled.

    Reply
  • The leaf level of each non-clustered index holds the clustering key (if you have a clustered index). So if you disable the clustered index, the non-clustered indexes wouldn’t be pointing to data (as they would be if you had a heap instead), they’d be pointing to the disabled index. It wouldn’t make sense to leave any non-clustered indexes enabled if you disable a clustered index. The behaviour is completely sensible.

    HTH,

    Regards,

    Greg

    Reply
  • I recently came across this need to disable all indexes for a massive data load. We do it nightly, so I’m interested in scripting this. When I try:
    ALTER INDEX ALL ON Production.Product DISABLE

    that works great, except that it also disables my PK index, which I don’t want to do, and which I don’t want to rebuild.

    I’m wondering if there is an equivalent:
    ALTER INDEX ALL_EXCEPT_PK ON Production.Product DISABLE

    and, likewise:
    ALTER INDEX ALL_EXCEPT_PK ON Production.Product REBUILD

    Maybe I’ll have to start writing SP’s to do this from the meta data, but I’d hoped someone else might have thought of this already?

    thanks!

    Reply
  • Hi, could you Please tell me , actually mai i have done MCA in 2008 and presently i am working in sqlserver 2005 as a developer in a small comany and i want adwise from your side about my carear and i want to get a specifice certifecation in database so plz tell me it is posible to get certifection and my maine role in my company as trainee ..

    Reply
  • NItin parthe
    July 9, 2010 6:00 pm

    all Indexes also can be rebuild in this way

    USE AdventureWorks;
    GO
    DBCC DBREINDEX (‘HumanResources.Employee’, ”, 70);
    GO

    Reply
  • u r best sir

    Reply
  • shaikferozbasha
    April 6, 2011 4:36 pm

    sir will u please help me to find the size of each nonclustered index in a table i got one query from u it showing the size of nonclustered index with the dataavailable in those columns but i need only size without data inthose indexes

    Reply
  • Great Post ! Thanks

    Reply

Leave a Reply