Thursday, December 13, 2018

Using table type and batch update statements in MS SQL


Given below is an example on how to use table types. Table types can be used to pass data array data from code to SQL stored procedure instead of passing the data in CSV or XML format. The example also shows how to batch SQL update statements.



CREATE TYPE [dbo].[BIGIntArrayTemp] AS TABLE
(
       [Val] [bigint] NULL
)
GO


DECLARE @tableType [dbo].[BIGIntArrayTemp]
insert into @tableType (val) values      (1)
insert into @tableType (val) values      (2)
insert into @tableType (val) values      (4)
insert into @tableType (val) values      (8)
insert into @tableType (val) values      (7)
insert into @tableType (val) values      (4)
insert into @tableType (val) values      (6)   
             
DECLARE @records TABLE(records_id INT IDENTITY(1,1),SomeId BIGINT)
DECLARE @toprecords TABLE(SomeId BIGINT)

DECLARE @tmp TABLE(SomeId BIGINT,flag bit)
insert into @tmp(SomeId) values   (1)
insert into @tmp(SomeId) values   (2)
insert into @tmp(SomeId) values   (4)
insert into @tmp(SomeId) values   (8)
insert into @tmp(SomeId) values   (7)
insert into @tmp(SomeId) values   (4)
insert into @tmp(SomeId) values   (6)
insert into @tmp(SomeId) values   (10)
      
INSERT INTO @records (SomeId)
SELECT        SomeId
FROM          @tmp t
INNER JOIN  @tableType ids ON ids.Val =  t.SomeId

DECLARE @count             INT
DECLARE @batch_size INT

SET @batch_size = 100 --set batch size

INSERT INTO @toprecords (SomeId)
       SELECT top (@batch_size) SomeId FROM @records ORDER BY records_id desc

SELECT @count = COUNT(1) FROM @toprecords

      
WHILE (@count > 0)
BEGIN
       BEGIN TRY 
              BEGIN TRANSACTION --Begin transaction for each batch
              UPDATE g
              SET g.flag=1
              FROM @tmp g
                     INNER JOIN @toprecords t
              ON t.SomeId = g.SomeId
                    
              -- clean toprecords table variable
              DELETE FROM @toprecords

              -- remove the top records
              DELETE FROM dd
              FROM (
                     SELECT top (@batch_size) * FROM @records ORDER BY records_id desc
              ) dd


              -- select next top records
              INSERT INTO @toprecords (SomeId)
                     SELECT top (@batch_size) SomeId FROM @records ORDER BY records_id desc

              SELECT @count = count(1) FROM @toprecords
              COMMIT TRANSACTION -- commit transaction for each batch

       END TRY 
       BEGIN CATCH 
                     ROLLBACK TRANSACTION -- rollback if any error occurs
       END CATCH

END

SELECT * FROM @tmp

------------------------------
Another way to implement it without try...catch and transaction


DECLARE @tableType [dbo].[BIGIntArrayTemp]
insert into @tableType (val) values      (1)
insert into @tableType (val) values      (2)
insert into @tableType (val) values      (4)
insert into @tableType (val) values      (8)
insert into @tableType (val) values      (7)
insert into @tableType (val) values      (4)
insert into @tableType (val) values      (6)  
            
DECLARE @records TABLE(records_id INT IDENTITY(1,1),SomeId BIGINT)
DECLARE @updatelist TABLE(SomeId BIGINT)

DECLARE @tmp TABLE(SomeId BIGINT,flag bit)
insert into @tmp(SomeId) values   (1)
insert into @tmp(SomeId) values   (2)
insert into @tmp(SomeId) values   (4)
insert into @tmp(SomeId) values   (8)
insert into @tmp(SomeId) values   (7)
insert into @tmp(SomeId) values   (4)
insert into @tmp(SomeId) values   (6)
insert into @tmp(SomeId) values   (10)
     

DECLARE @count             INT
DECLARE @batch_size INT,@RecordCount INT

SET @batch_size = 2 --set batch size

INSERT INTO @records (SomeId)
       SELECT        SomeId
       FROM          @tmp t
       INNER JOIN  @tableType ids ON ids.Val =  t.SomeId
         
Select @RecordCount = @@RowCount --initialize RecordCount
     
WHILE (@RecordCount > 0)
BEGIN
       Delete top(@batch_size) r
       Output deleted.SomeId Into @updatelist
       From @records r

       Select @RecordCount = @@RowCount --update record count variable
       PRINT ('Record Count  --'+ convert(varchar,@RecordCount))
         
    UPDATE g
    SET g.flag=1
    FROM @tmp g
            INNER JOIN @updatelist t
    ON t.SomeId = g.SomeId
                   
    -- clean toprecords table variable
    Delete from @updatelist

    Waitfor Delay '00:00:00.5'

END

SELECT * FROM @tmp


--------------------------------

Table type data can be used in stored procedure to pass array data.

CREATE PROCEDURE [dbo].[xyz] 
       @ids dbo.[BIGIntArrayTemp] READONLY
AS 
BEGIN
       -- Proc body
END