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;