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.