SQL SERVER – Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index

This blog is in response to the ongoing communication with the reader who had earlier asked the question of SQL SERVER – Disable Clustered Index and Data Insert. The same reader has asked me the difference between ALTER INDEX ALL REBUILD and ALTER INDEX REBUILD along with disabled clustered index.

Instead of writing a big theory, we will go over the demo right away. Here are the steps that we intend to follow.

1) Create Clustered and Nonclustered Index
2) Disable Clustered and Nonclustered Index
3) Enable – a) All Indexes, b) Clustered Index

USE tempdb
GO
-- Drop Table if Exists
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[TableName]') AND type IN (N'U'))
DROP TABLE [dbo].[TableName] GO
-- Create Table
CREATE TABLE [dbo].[TableName](
[ID] [int] NOT NULL,
[FirstCol] [varchar](50) NULL
)
GO
-- Create Clustered Index
ALTER TABLE
[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
([ID] ASC)
GO
-- Create Nonclustered Index
CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] ([FirstCol] ASC)
GO
-- Check that all the indexes are enabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO

SQL SERVER - Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index is_disabled1

Now let us disable both the indexes.

-- Disable Indexes
-- Disable Nonclustered Index
ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE
GO
-- Disable Clustered Index
ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE
GO
-- Check that all the indexes are disabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO

SQL SERVER - Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index is_disabled2

Next, let us rebuild all the indexes and see the output.

-- Test 1: ALTER INDEX ALL REBUILD
-- Rebuliding should work fine
ALTER INDEX ALL ON [dbo].[TableName] REBUILD
GO
-- Check that all the indexes are enabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO

SQL SERVER - Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index is_disabled1

Now, once again disable indexes for the second test.
-- Disable Indexes
-- Disable Nonclustered Index
ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE
GO
-- Disable Clustered Index
ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE
GO
-- Check that all the indexes are disabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO

SQL SERVER - Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index is_disabled2

Next, let us build only the clustered index and see the output of all the indexes.
-- Test 2: ALTER INDEX REBUILD
-- Rebuliding should work fine
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD
GO
-- Check that only clustered index is enabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO

SQL SERVER - Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index is_disabled3

Let us do final clean up.
-- Clean up
DROP TABLE [TableName] GO

From the example, it is very clear that if you have built only clustered index when the nonclustered index is disabled, it still remains disabled. Do let me know if the idea is clear.

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

,
Previous Post
SQL SERVER – Spatial Database Queries – What About BLOB
Next Post
SQL SERVER – Four Posts on Removing the Bookmark Lookup – Key Lookup

Related Posts

12 Comments. Leave new

  • Hi Pinal,

    Thanks for the explanation.As I understand Rebuilding an index will drop the index and will re-create the same which is causing this behavior.

    Thank You,
    Anup

    Reply
    • Marko Parkkola
      May 18, 2010 11:46 am

      I think rebuild does not actually drop the index. It just, well, rebuilds it and enables it.

      Reply
  • Hi Pinal,

    A good article on SQL Server Indexes to clear all the doubts. Appreciate it!

    There is a little typo in the last paragraph. I have read it, and read it again to convince that there is not, but could not help myself much.

    The last para says “From the example, it is very clear that if you have *built* only clustered index when the nonclustered index is disabled, it still remains disabled. Do let me know if the idea is clear. ”

    which confused me *a bit* initially until I read the whole write-up again.

    It should have been “From the example, it is very clear that if you have *rebuilt* only clustered index when the nonclustered index is disabled, it still remains disabled. Do let me know if the idea is clear. ”

    May be you already have had received same response from your readers, I am perhaps not aware, didnt check all the replies for this!

    Just thought to bring it to your kind notice, to make your write-up more robust.

    Thanks & Regards!
    Sumit M.

    Reply
  • I want to alter all the Non-Clustered indexes to Clusterd, what will be the Query Please help.

    Reply
  • Dave from Sydney
    April 18, 2012 9:01 am

    Nice article. I notice also that if you just disable the clustered index you get the following message:-

    Warning: Index ‘IX_NonClustered_TableName’ on table ‘TableName’ was disabled as a result of disabling the clustered index on the table.

    So if you disable the clustered index it automatically disables the non-clustered index, but if you rebuild the clustered index the non-clustered doesn’t get automatically rebuilt. Is that what you are getting at?

    Reply
    • Yes ….I too notice same thing We we disable cluster index non-cluster index also get disabled however vice versa is not true.

      Reply
  • once again thanks you Dave sir For indexing Chapter

    Reply
  • sir i need example of trigger i search on google bu i cant ca you just mail me your trigger link

    Reply
  • Excellent!!!! Thanks!!!

    Reply
  • Rajesh Vungarala
    June 11, 2015 10:27 am

    Hi Pinal

    How can we Modified Clustered index without disabling or Deleting Existing primary key index

    Reply
  • Excellent!!! thanx!!

    Reply
  • Hi Pinal,

    I have one doubt please clarify.

    I have create one procedure it will take around 10 Minutes. After some time we have done some changes in the procedure and execute the procedure it will take around 1 hour. And same procedure we have run again it will take 10 minutes.
    Please explain..
    Thanks in advanced…

    Reply

Leave a Reply

Menu