Applies to SQL Server 2019, 2022 and 2025.

SQL Server disabled indexes are handy and a bit dangerous. ALTER INDEX ... DISABLE keeps the index definition but throws away its data, and what that means depends entirely on whether the index is nonclustered or clustered. I use it most before big loads and when I want to test whether an index is earning its keep without losing the script for it.

What disabling an index actually does

When you disable an index, SQL Server keeps its definition in metadata and stops maintaining it. The optimiser ignores it, and any query that names it in an index hint fails instead of quietly using something else.

• Nonclustered index: the index pages are deallocated, so you get the space back. The table carries on as normal.
• Clustered index: the rows stay on disk, but the table can’t be read or changed until the clustered index is rebuilt or dropped. Every nonclustered index on the table is disabled with it, and so are the foreign keys into and out of the table.
• Unique index or primary key: the constraint is disabled too, along with any foreign keys that point at it.

There is no ENABLE keyword. You bring an index back by rebuilding it, which builds it from scratch, so it costs as much as creating it in the first place.

Finding SQL Server disabled indexes

Disabled indexes are easy to forget about. This lists them in the current database:

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
       OBJECT_NAME(i.object_id)        AS TableName,
       i.name                          AS IndexName,
       i.type_desc                     AS IndexType,
       i.is_unique                     AS IsUnique
FROM sys.indexes AS i
WHERE i.is_disabled = 1
ORDER BY SchemaName, TableName, IndexName;

A worked example

Run this in a scratch database. It builds a small table with a clustered primary key and one nonclustered index.

CREATE TABLE dbo.DisableDemo
(
    DemoId     int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DisableDemo PRIMARY KEY CLUSTERED,
    CustomerId int NOT NULL,
    Amount     decimal(10,2) NOT NULL
);
CREATE NONCLUSTERED INDEX IX_DisableDemo_CustomerId ON dbo.DisableDemo (CustomerId);

INSERT dbo.DisableDemo (CustomerId, Amount)
VALUES (1, 10.00), (2, 25.50), (1, 7.25);

-- 1. Disable the nonclustered index: the table still works
ALTER INDEX IX_DisableDemo_CustomerId ON dbo.DisableDemo DISABLE;
SELECT CustomerId, Amount FROM dbo.DisableDemo WHERE CustomerId = 1;

-- Bring it back
ALTER INDEX IX_DisableDemo_CustomerId ON dbo.DisableDemo REBUILD;

-- 2. Disable the clustered index: the table is now unreadable
ALTER INDEX PK_DisableDemo ON dbo.DisableDemo DISABLE;
SELECT CustomerId, Amount FROM dbo.DisableDemo;  -- fails: index is disabled

-- Rebuilding just the clustered index leaves the nonclustered one disabled
ALTER INDEX PK_DisableDemo ON dbo.DisableDemo REBUILD;
SELECT name, is_disabled FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.DisableDemo');

-- ALL rebuilds and enables everything on the table
ALTER INDEX ALL ON dbo.DisableDemo REBUILD;

DROP TABLE dbo.DisableDemo;

The second SELECT fails with an error saying the query processor can’t produce a plan because the index is disabled. The data is still there; you just can’t get at it.

The common mistake

The mistake I see most is ALTER INDEX ALL ON dbo.SomeTable DISABLE before a bulk load on a table with a clustered index. The idea is right (don’t maintain indexes row by row during a big insert), but ALL includes the clustered index, so the table becomes unreadable and unwritable, and the load you were trying to speed up fails straight away.

Only disable the nonclustered indexes. This builds the statements for one table rather than running them, so you can check them first:

SELECT N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON '
     + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(i.object_id))
     + N' DISABLE;' AS DisableCommand
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.SomeTable')
  AND i.type_desc = N'NONCLUSTERED'
  AND i.is_disabled = 0
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0;

I leave primary key and unique constraint indexes alone even when they’re nonclustered, because disabling them switches off the constraint and the foreign keys that depend on it. After the load, ALTER INDEX ALL ON dbo.SomeTable REBUILD; brings everything back in one go.

The second half of the mistake is assuming a rebuild puts everything back as it was. It doesn’t re-enable foreign keys. They stay disabled until you switch them back on, and you want WITH CHECK so SQL Server validates the data and trusts the constraint again:

ALTER TABLE dbo.SomeChildTable WITH CHECK CHECK CONSTRAINT FK_SomeChildTable_SomeTable;

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE is_disabled = 1 OR is_not_trusted = 1;

Watch out for

• Disabling a unique index means nothing enforces uniqueness until it’s rebuilt. If duplicates get in meanwhile, the rebuild fails.
• Rebuilding only the clustered index leaves the nonclustered indexes disabled. Use ALTER INDEX ALL ... REBUILD or rebuild each one.
• Indexes on views that reference the table are disabled with its clustered index, and need their own ALTER INDEX ALL ... REBUILD on the view.
• A disabled clustered index has to be rebuilt offline. A disabled nonclustered index can be rebuilt online (Enterprise edition) as long as the clustered index isn’t disabled too.
• Index maintenance jobs differ in how they treat disabled indexes, so don’t count on the weekly job to bring one back. Check is_disabled yourself.
• You can’t create a new index with the same name as a disabled one. Drop it or rebuild it.

For finding indexes that might not be needed in the first place, see Performance 09: duplicate and overlapping indexes. More index and maintenance scripts are in the SQL Server DBA Handbook. Microsoft’s reference pages are Disable indexes and constraints and Enable indexes and constraints.

This topic came up in the SQLServerCentral Question of the Day: The Disabled Index.

Clive Roberts DBA