Have you ever encountered a situation where a conversation never seems to end, even though the original point of discussion has passed? I am facing a similar situation with Disabled Index in SQL Server.

When we disable an index in SQL Server, it raises questions about its impact on statistics. Does a disabled index continue to update the statistics? Let’s explore this interesting concept.

First, let’s understand the behavior of disabled indexes in SQL Server. If you have a disabled clustered index, you will not be able to update the statistics for any index on the table. However, if you have an enabled clustered index and a disabled non-clustered index, updating the statistics of the table will automatically update the statistics for both the disabled and enabled indexes.

Let’s walk through a simple example to illustrate this behavior:

USE tempdb

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

-- Create Table
CREATE TABLE [dbo].[TableName](
[ID] [int] NOT NULL,
[FirstCol] [varchar](50) NULL
)

-- Insert Some data
INSERT INTO TableName
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third'
UNION ALL
SELECT 4, 'Fourth'
UNION ALL
SELECT 5, 'Five'

-- Create Clustered Index
ALTER TABLE [TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
([ID] ASC)

-- Create Nonclustered Index
CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_TableName] ON [dbo].[TableName]
([FirstCol] ASC)

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

-- Update the stats of table
UPDATE STATISTICS TableName
WITH FULLSCAN

-- Check Statistics Last Updated Datetime
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('TableName')

-- Disable Indexes
-- Disable Nonclustered Index
ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE

-- Disable Clustered Index
ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE

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

-- Update the stats of table
UPDATE STATISTICS TableName
WITH FULLSCAN

When we try to update the statistics with the disabled clustered index, it throws an error because the clustered index is disabled. However, if we enable the clustered index and update the statistics, it will update the statistics for both the disabled and enabled indexes.

-- Now let us rebuild clustered index only
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD

-- Check the status of all the indexes
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'

-- Check Statistics Last Updated Datetime
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('TableName')

-- Update the stats of table
UPDATE STATISTICS TableName
WITH FULLSCAN

-- Check Statistics Last Updated Datetime
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('TableName')

As you can see, even though the non-clustered index is disabled, it is still updated when we update the statistics. If you do not need a non-clustered index, it is recommended to drop it, as keeping disabled indexes can be an overhead on your system. This is because every time the statistics are updated for the system, all the statistics for disabled indexes are also updated.

Understanding the behavior of disabled indexes and their impact on statistics is crucial for optimizing the performance of your SQL Server database. By managing your indexes effectively, you can ensure that your queries run efficiently and provide accurate results.

Remember to clean up after running the example script:

-- Clean up
DROP TABLE [TableName]

By understanding disabled indexes and their relationship with statistics, you can make informed decisions when managing your SQL Server database.