Disable non-clustered indexes during data load
Indexes can slow down INSERTS into SQL Server tables, especially in Azure SQL where bulk logged inserts are not available.
This procedure can disable and then rebuild all non-clustered indexes on one specified table.
Disabling non-clustered indexes is a zero cost operation; the index data is dropped but the metadata remains. Re-enabling the non-clustered indexes (REBUILD) does of course have a cost so testing is necessary to confirm that there is a net saving for a particular bulk insert.
Limitation for compressed indexes When an index is disabled its compression settings are lost. Rebuilding will result in an uncompressed index unless a compression option is specified. Therefore this procedure will remove any compression from indexes when used. Working round this is something of a challenge: the index compression setting has to be spirited away somewhere (e.g. in extended properties on the table) and then read back when the index is rebuilt. This is a challenge yet to be addressed in this procedure.
The procedure uses the Error Handler for T-SQL.
AlterNonClusteredIndexes procedure
CREATE PROCEDURE [dbo].[AlterNonClusteredIndexes] /* Carry out the specified action on all the nonclustered indexes on a specifed table. PARAMETERS @Schema and @Table specify the table. @Action should be one of 'DISABLE', 'REBUILD', 'REORGANIZE' */ @Schema NVARCHAR(250), @Table NVARCHAR(250), @Action NVARCHAR(50) AS DECLARE @SchemaAndTable NVARCHAR(510); DECLARE @TableObjectId INT; DECLARE @DebugInfo VARCHAR(2000); DECLARE @SQL NVARCHAR(4000); SET XACT_ABORT ON; SET NOCOUNT ON; BEGIN TRY SET @DebugInfo = '@Schema=' + ISNULL('''' + @Schema + '''', 'NULL') + ' @Table=' + ISNULL('''' + @Table + '''', 'NULL') + '@Action=' + ISNULL('''' + @Action + '''', 'NULL'); IF (@Action NOT IN ( 'DISABLE', 'REBUILD', 'REORGANIZE' )) RAISERROR('@Action must be one of DISABLE, REBUILD, REORGANIZE.', 16, 1); SET @Schema = IIF(LEFT(@Schema, 1) = '[', @Schema, QUOTENAME(@Schema)); SET @Table = IIF(LEFT(@Table, 1) = '[', @Table, QUOTENAME(@Table)); SET @SchemaAndTable = @Schema + N'.' + @Table; SELECT @TableObjectId = OBJECT_ID(@SchemaAndTable); IF (@TableObjectId IS NULL) RAISERROR ('Table not found (OBJECTID(@Schema.@Table) returned Null).', 16, 1); SELECT @SQL = CONCAT(@SQL, N'ALTER INDEX [' + [name] + N'] ON ' + @SchemaAndTable + N' ' + @Action + '; ') FROM sys.indexes i WHERE i.object_id = @TableObjectId AND i.type_desc = N'NONCLUSTERED'; IF (@SQL IS NOT NULL) BEGIN EXEC sp_executesql @stmt = @SQL; END; END TRY BEGIN CATCH EXEC dbo.ERROR_HandleCaughtError @DebugInfo = @DebugInfo; RETURN 5555 END CATCH;