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

Right click on Indexes and select Rebuild All

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

About these ads

12 thoughts on “SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact

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

    Like

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

    Like

  3. 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!

    Like

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

    Like

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

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

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

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