Disable or rebuild all nonclustered indexes on a table

, 2019-02-26 (first published: )

I use this when inserting data into a staging table on  a data warehouse. 

If inserting data into an empty table it is faster to insert data when the indexes are disabled and then rebuild the indexes once the insert has completed than to insert all the data with the indexes enabled.

If you disable a clustered index then you won't be able to insert any data onto the table. So this script only disables and then rebuilds nonclustered indexes.

DECLARE @EnableOrRebuild as nvarchar(20) 
SET @EnableOrRebuild = 'DISABLE'  
/* SET @EnableOrRebuild = 'REBUILD' */ -- Uncomment for REBUILD
DECLARE @TableName as nvarchar(200) = 'myTable' -- Enter your table name here
DECLARE @SchemaName as nvarchar(200) = 'dbo' -- Enter the schema here
DECLARE @Sql AS nvarchar(MAX)='';

SELECT @Sql = @Sql + N'ALTER INDEX ' + quotename(i.name) + N' ON ' + quotename(s.name) + '.' + quotename(o.name) + ' ' + @EnableOrRebuild + N';' + CHAR(13) + CHAR(10)
  FROM sys.indexes i
 INNER JOIN sys.objects o ON i.object_id = o.object_id
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE i.type_desc = N'NONCLUSTERED'
   AND o.type_desc = N'USER_TABLE'
   AND o.name = @TableName 
   AND s.name = @SchemaName

EXEC (@Sql);


4.33 (3)




4.33 (3)

Related content

Resumable Online Index Create and Rebuild Operations

Index maintenance can be a real headache for database administrators as tables grow larger and maintenance windows shrink. In this article, Greg Larsen demonstrates resumable index operations available with SQL Server 2017 and 2019. This feature helps DBAs work around those small maintenance windows by allowing certain index operations to be paused and restarted again later.


1,738 reads

What are Inline Indexes?

New releases of SQL Server arrive at a quick pace, and it's difficult to keep up with the many features introduced in each version. In this article, Phil Factor reviews a feature you may have missed, inline indexes. He covers the syntax and the many ways they can be used and then performs some performance tests to see if they can make a difference with table variables.


3,669 reads