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
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
I recommend using explicit transactions if you are performing INSERT/UPDATE/DELETE.