SQL Server – Disabling Every Index on a Table

From SQL Server 2005 onwards, disabling a specific index on a table is very straightforward. This can be achieved by the following:

ALTER INDEX <index name> ON <table name> DISABLE

Then to re-enable the index:

ALTER INDEX <index name> ON <table name> REBUILD

To disable or rebuild all the indexes on a table, replace <index name> in the above statements with the keyword ALL, as follows:

ALTER INDEX ALL ON <table name> REBUILD

An interesting point is that if a table has a clustered index and this clustered index is disabled, all other indexes are disabled. Of more interest is that fact that once the clustered index is disabled, you can’t access the data or insert into the table or in actual fact do anything else except drop or rebuild the index! If someone can explain why anyone would want to disable the clustered index when it has this effect, I’d be very happy to hear why.

Another point to note is that when the clustered index is re-enabled, the other indexes are not automatically re-enabled with it.

The point of this post is that sometimes there is a need to disable all the non-clustered indexes on a particular table, probably to improve performance when doing an insert of a large number of rows. Of course, you can add several “ALTER INDEX .. DISABLE” lines to a script, but it’s more convenient to not have to worry about which indexes exist and what their names are.

The following T-SQL script will perform this function, disabling all the non-clustered indexes on a particular table and re-enabling them after doing some work:

-- Set the name of the schema and table here
DECLARE @Schema sysname
SET @Schema = 'dbo'
DECLARE @Table sysname
SET @Table = 'TableWithSomeIndexes'
 
-- Get the non-clustered indexes
DECLARE @Indexes TABLE(Name sysname)
INSERT INTO @Indexes(Name)
SELECT ind.name
FROM sys.indexes ind
WHERE ind.object_id = OBJECT_ID(@SCHEMA + '.' + @TABLE)
AND ind.Type != 1 -- 1 is clustered
AND ind.is_disabled = 0
 
-- Disable the indexes
DECLARE @sql1 NVARCHAR(MAX)
SELECT @sql1 = isnull(@sql1, '') + 'ALTER INDEX ' + name + ' ON ' + @SCHEMA + '.' + @TABLE + ' DISABLE '
FROM @Indexes
EXEC SP_EXECUTESQL @sql1
 
-- Do your work here....
/*
INSERT INTO EmptyTable
SELECT LotsOfRows
FROM ComplicatedQuery
*/
 
-- Now we re-enable the indexes that we disabled
DECLARE @sql2 NVARCHAR(MAX)
SELECT @sql2 = isnull(@sql2, '') + 'ALTER INDEX ' + name + ' ON ' + @SCHEMA + '.' + @TABLE + ' REBUILD '
FROM @Indexes
EXEC SP_EXECUTESQL @sql2

The script has been tested on SQL Server 2008 but should work on SQL Server 2005.