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

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

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

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

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

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 (http://blog.SQLAuthority.com)

15 thoughts on “SQL SERVER – Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index

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

    Like

  2. Pingback: SQL SERVER – Disabled Index and Update Statistics Journey to SQL Authority with Pinal Dave

  3. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

  4. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

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

    Like

  6. Pingback: SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31 « SQL Server Journey with SQL Authority

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

    Like

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

      Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | 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