Today, we will discuss the difference between ALTER INDEX ALL REBUILD and ALTER INDEX REBUILD in SQL Server. These two commands are used to rebuild indexes in a database. To better understand the concept, let’s dive into a practical example.

First, we will create a clustered index and a nonclustered index on a table. Then, we will disable both indexes. Let’s see the steps:

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

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

After disabling the indexes, we can check their status using the following query:

SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO

Now, let’s move on to the first test. We will rebuild all the indexes using the ALTER INDEX ALL REBUILD command:

-- Test 1: ALTER INDEX ALL REBUILD
-- Rebuilding 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

As you can see, all the indexes are enabled after rebuilding them. However, if we disable the indexes again and only rebuild the clustered index using the ALTER INDEX REBUILD command, the result is different:

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

-- Test 2: ALTER INDEX REBUILD
-- Rebuilding should work fine
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD
GO

-- Check that only the clustered index is enabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO

From the example, it is clear that if you only rebuild the clustered index when the nonclustered index is disabled, the nonclustered index remains disabled.

Understanding the difference between ALTER INDEX ALL REBUILD and ALTER INDEX REBUILD is important when managing indexes in SQL Server. Depending on your requirements, you can choose the appropriate command to rebuild your indexes efficiently.

I hope this article has provided you with a clear understanding of ALTER INDEX ALL REBUILD and ALTER INDEX REBUILD in SQL Server. If you have any further questions or need clarification, please let me know.