I needed to update a substantial amount of data in our production environment. To minimize table lock time and prevent the logfile from growing excessively, I created a batch update script. While the script is running, you can monitor the progress in the message window.

For this task, I utilized the AdventureWorks2016CTP3 database. The script's purpose is to update the "EventDateTime" column by adding one day to it for all records with a date greater than 2014-01-01.

This script applies to SQL Server 2016.

use AdventureWorks2016CTP3
GO
SET NOCOUNT ON;

DROP TABLE IF EXISTS #stage
CREATE TABLE #stage
(
    Id INT
   ,IsUpdated BIT default 0
   ,NewValue datetime2
)
INSERT INTO #stage (Id, NewValue)
SELECT 
 OrderTrackingID
,EventDateTime = dateadd(day,-1,EventDateTime)
FROM Sales.OrderTracking
where EventDateTime > '2014-01-01'
ORDER BY OrderTrackingID ASC

CREATE NONCLUSTERED INDEX ix_stage_id on #stage(Id) include (NewValue, IsUpdated)
CREATE NONCLUSTERED INDEX ix_stage_IsUpdated on #stage(IsUpdated) include (Id, NewValue)
GO

DECLARE @TotalBatchDurationTime DATETIME = GETDATE()
DECLARE @TotalAffectedRows INT = 0
DECLARE @TotalRows INT = (SELECT COUNT(*) from #stage where IsUpdated = 0) 
DECLARE @RowsToGo INT
DECLARE @BatchSize INT = 2000
DECLARE @starttime DATETIME
DECLARE @msg VARCHAR(512)
DECLARE @cnt int

WHILE EXISTS (SELECT TOP (1) 1 FROM #stage where IsUpdated = 0)
BEGIN 
  SET @starttime = GETDATE()

  DROP TABLE IF EXISTS #StageOutput
  CREATE TABLE #StageOutput (Id int)

  UPDATE TOP (@BatchSize) o
  SET o.EventDateTime = s.NewValue
  OUTPUT inserted.OrderTrackingID INTO #StageOutput(Id)
  FROM Sales.OrderTracking AS o
  join #stage as s on o.OrderTrackingID = s.Id 
  where IsUpdated = 0

  UPDATE #stage
  SET  IsUpdated = 1
  WHERE Id in (select Id from #StageOutput)

  set @cnt = @@ROWCOUNT
  set @TotalAffectedRows = @TotalAffectedRows+@cnt
  set @RowsToGo = @TotalRows-@TotalAffectedRows

  SELECT @msg = CAST(@cnt AS VARCHAR(10)) + ' rows updated '+CHAR(10)
  +'batch duration: '+ CAST(DATEDIFF(MILLISECOND,@starttime, GETDATE()) AS VARCHAR)+'(ms)'+char(10)
  +'rows to go:  ' +CAST(@RowsToGo AS VARCHAR)+CHAR(10)

  RAISERROR(@msg, 0, 1) WITH NOWAIT
END
PRINT '-----------------------------'
+char(10)+'total batch duration: '+CAST(DATEDIFF(SECOND,@TotalBatchDurationTime, GETDATE()) AS VARCHAR)+'(sec)'
+char(10)+'total affected rows: ' +CAST(@TotalAffectedRows AS VARCHAR)
+char(10)+'-----------------------------'
+char(10)+'DONE!'

DROP TABLE #stage
DROP TABLE #StageOutput
GO