Incrementally load data from multiple tables in SQL Server to an Azure SQL database

Incrementally load data from multiple tables in SQL Server to an Azure SQL database

I found it really helpful article for people who are beginners on Azure Data Factory - ADF.

Microsoft has provided detailed step by step instruction on below steps on this documentation:

  • Prepare source and destination data stores.
  • Create a data factory.
  • Create a self-hosted integration runtime.
  • Install the integration runtime.
  • Create linked services.
  • Create source, sink, and watermark datasets.
  • Create, run, and monitor a pipeline.
  • Review the results.
  • Add or update data in source tables.
  • Rerun and monitor the pipeline.
  • Review the final results.

Check out article here - https://docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-multiple-tables-portal

Also, check out Azure-SSIS integration Runtime (IR) in Azure Data Factory - https://docs.microsoft.com/en-us/azure/data-factory/tutorial-deploy-ssis-packages-azure

Full Detailed - Data Factory Documentation

https://docs.microsoft.com/en-us/azure/data-factory/

Would you disable CLUSTERED index before bulk insert? Disabling only NON-CLUSTERED indexes for load performance.

Would you disable CLUSTERED index before bulk insert? Disabling indexes for load performance.

It is a good practice to disable indexes before mass insert. However, before you disable index, ask yourself a question – Do you really need to disable clustered and all non-clustered indexes?

I recommend do not drop or disable clustered index. By doing so, you are making your table unavailable and in result, you cannot do insertion process.

Let’s say you have disabled clustered index with below command now what?
ALTER INDEX I_USERPK ON dbo.USER DISABLE
GO

It worked but with lots of warning. It has also disabled all non-clustered indexes on table as well.

Now if you run insert or select statement on USER table, you will get below error
The query processor is unable to produce a plan because the index 'I_USERPK' on table or view 'USER' is disabled.

Simple you have made your table unavailable.
I would say never disable clustered index on table and it is good practice to disable non-clustered index before insertion.

Below are stored procedure to disable and enable non-clustered indexes on single table or all tables on DB.

Disable non-clustered indexes with stored procedure - with help of cursor example

CREATE PROCEDURE [dbo].[disableAllIndexes]
--ALTER PROCEDURE [dbo].[disableAllIndexes]
    @table_name VARCHAR(15) –parameter to pass table name as argument

AS
BEGIN

    --START MSSQL Transaction
    BEGIN TRANSACTION;

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    --DECLARE variables here
    DECLARE @alter_query NVARCHAR(400)

    --DECLARE AND SET COUNTER.
    DECLARE @Counter INT
    SET @Counter = 0

    --TRY block
    BEGIN TRY

    --Declare Cursor
    DECLARE index_cursor_disable CURSOR FOR
        SELECT N'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' DISABLE'
        FROM sys.indexes I
        INNER JOIN sys.tables T ON I.object_id = T.object_id
        WHERE I.type_desc = 'NONCLUSTERED'
        AND I.name IS NOT NULL
        AND I.is_disabled = 0
        AND T.name LIKE @table_name

    -- OPEN cursor
    OPEN index_cursor_disable

    -- FETCH records into variable to run alter query
    FETCH NEXT FROM index_cursor_disable INTO @alter_query

    --LOOP UNTIL RECORDS ARE AVAILABLE
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Counter = @Counter + 1

        PRINT @alter_query
        EXECUTE sp_executesql @alter_query

        --FETCH NEXT RECORD INTO THE VARIABLES.
        FETCH NEXT FROM index_cursor_disable INTO @alter_query
    END

    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN

            --CLOSE THE CURSOR.
            CLOSE index_cursor_disable
            DEALLOCATE index_cursor_disable

            DECLARE @ErrorNumber VARCHAR(50),@ErrorProcedure VARCHAR(50), @ErrorLine VARCHAR(100), @ErrorMessage VARCHAR(400),
                @ErrorSeverity VARCHAR(100), @ErrorState VARCHAR(100)
            SELECT @ErrorNumber = ERROR_NUMBER();
            SELECT @ErrorProcedure = ERROR_PROCEDURE()
            SELECT @ErrorLine = ERROR_LINE()
            SELECT @ErrorMessage = ERROR_MESSAGE()
            SELECT @ErrorSeverity = ERROR_SEVERITY()
            SELECT @ErrorState = ERROR_STATE()

            PRINT 'ROLLING BACK TRANSACTION'
            PRINT 'ErrorProcedure - '+@ErrorProcedure
            PRINT 'ErrorNumber - '+@ErrorNumber
            PRINT 'ErrorLine - '+@ErrorLine
            PRINT 'ErrorMessage - '+@ErrorMessage
            PRINT 'ErrorSeverity - '+@ErrorSeverity
            PRINT 'ErrorState - '+@ErrorState

            ROLLBACK TRANSACTION
        END

    END CATCH

    IF @@TRANCOUNT > 0
    BEGIN
        --CLOSE THE CURSOR.
        CLOSE index_cursor_disable
        DEALLOCATE index_cursor_disable

        PRINT CAST(@Counter AS VARCHAR) +' Indexes Found'
        PRINT 'All above listed NONCLUSTERED INDEXES are disabled'
        COMMIT TRANSACTION
    END
END

Jaymin Soni

Find and rebuild all NON-CLUSTERED indexes in selected database with stored procedure (cursor example)

CREATE PROCEDURE [dbo].[rebuildAllIndexes]
--ALTER PROCEDURE [dbo].[rebuildAllIndexes]

AS
BEGIN

    --START MSSQL Transaction
    BEGIN TRANSACTION;

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;

    --DECLARE variables here
    DECLARE @alter_query NVARCHAR(400)

    --TRY block
    BEGIN TRY

    --Declare Cursor
    DECLARE index_cursor_rebuild CURSOR FOR
        SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' REBUILD'
        FROM sys.indexes I
        INNER JOIN sys.tables T ON I.object_id = T.object_id
        WHERE I.type_desc = 'NONCLUSTERED'
        AND I.name IS NOT NULL
        AND I.is_disabled = 1

    --DECLARE AND SET COUNTER.
    DECLARE @Counter INT
    SET @Counter = 0

    -- OPEN cursor
    OPEN index_cursor_rebuild

    -- FETCH records into variable to run alter query
    FETCH NEXT FROM index_cursor_rebuild INTO @alter_query

    --LOOP UNTIL RECORDS ARE AVAILABLE
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Counter = @Counter + 1
        PRINT @alter_query
        EXECUTE sp_executesql @alter_query

        --FETCH NEXT RECORD INTO THE VARIABLES.
        FETCH NEXT FROM index_cursor_rebuild INTO @alter_query

    END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN

            --CLOSE THE CURSOR.
            CLOSE index_cursor_rebuild
            DEALLOCATE index_cursor_rebuild

            DECLARE @ErrorNumber VARCHAR(50),@ErrorProcedure VARCHAR(50), @ErrorLine VARCHAR(100), @ErrorMessage VARCHAR(400),
                @ErrorSeverity VARCHAR(100), @ErrorState VARCHAR(100)
            SELECT @ErrorNumber = ERROR_NUMBER();
            SELECT @ErrorProcedure = ERROR_PROCEDURE()
            SELECT @ErrorLine = ERROR_LINE()
            SELECT @ErrorMessage = ERROR_MESSAGE()
            SELECT @ErrorSeverity = ERROR_SEVERITY()
            SELECT @ErrorState = ERROR_STATE()

            PRINT 'ROLLING BACK TRANSACTION'
            PRINT 'ErrorProcedure - '+@ErrorProcedure
            PRINT 'ErrorNumber - '+@ErrorNumber
            PRINT 'ErrorLine - '+@ErrorLine
            PRINT 'ErrorMessage - '+@ErrorMessage
            PRINT 'ErrorSeverity - '+@ErrorSeverity
            PRINT 'ErrorState - '+@ErrorState

            ROLLBACK TRANSACTION
        END

    END CATCH

    IF @@TRANCOUNT > 0
    BEGIN
        --CLOSE THE CURSOR.
        CLOSE index_cursor_rebuild
        DEALLOCATE index_cursor_rebuild

        PRINT CAST(@Counter AS VARCHAR) +' Indexes Found'
        PRINT 'All above listed NONCLUSTERED INDEXES are rebuilded'
        COMMIT TRANSACTION
    END
END

How to avoid cursor in SQL Server stored procedures to loop over records?

How to avoid cursor in SQL Server stored procedures to loop over records?

Yes, I agree, not to use cursor in MSSQL stored procedures. It is bad practice to use cursor in SQL server as cursor takes up memory and create extending locks. As you loop records through cursor, you are adding result rows into memory and locking them and as you go through rows, you might making changes to other tables and still keeping all of the memory and locks of the cursor open. Cursor can cause serious performance issue and it affects other users working on same server. Below alternate method, you can use to avoid cursor.

USE TEMP TABLE WITH IDENTITY COLUMN

CREATE PROCEDURE [dbo].[cursorAlternate]
AS
BEGIN
         SET NOCOUNT ON;

         --declare variables here
         DECLARE
                 @i INT, -- iterator
                 @rowCount INT, –row counter
                 @customerName VARCHAR(50) –looping value

         --create temp table to store result set to loop through
         CREATE TABLE #loopTable (ID INT IDENTITY(1,1), CUSTOMER_NAME VARCHAR(50))

         --insert result set to tamp table instead of cursor
         INSERT INTO #loopTable (CUSTOMER_NAME)
         SELECT customer_name
         FROM CUSTOMER

         --count number of rows
         SET @rowCount = @@ROWCOUNT --SCOPE_IDENTITY() would also work

         /* Its better to add index in at one time after insert. It is always faster than to update the index as you write into the temp table. */
         CREATE CLUSTERED INDEX idx_tmp on #loopTable (ID) WITH FILLFACTOR = 100

         --LOOP UNTIL RECORDS ARE AVAILABLE.
         WHILE @i<=@rowCount
         BEGIN
                 SELECT @customerName = CUSTOMER_NAME
                 FROM #loopTable
                 WHERE ID = @id

                 -- Begin Transactions
                 -- Enter all your operations/Transactions here
                 PRINT @customerName
                 -- Commit Transactions

                 SET @i = @i + 1 – incremental

         END

         DROP TABLE#loopTable

END

Jaymin Soni

I recommend using explicit transactions if you are performing INSERT/UPDATE/DELETE.