MSSQL update table with million of rows.

[sapator]

Hi.
MSSQL.
I need to update a table with million of rows.
I was thinking to do a pass on rowbatch at a time, so i found this script on stackoverf.

DECLARE @Rows INT,
        @BatchSize INT; -- keep below 5000 to be safe
    
SET @BatchSize = 2000;

SET @Rows = @BatchSize; -- initialize just to enter the loop

BEGIN TRY    
  WHILE (@Rows = @BatchSize)
  BEGIN
      UPDATE TOP (@BatchSize) tab
      SET    tab.Value = 'abc1'
      FROM  TableName tab
      WHERE tab.Parameter1 = 'abc'
      AND   tab.Parameter2 = 123
      AND   tab.Value <> 'abc1' COLLATE Latin1_General_100_BIN2;
      -- Use a binary Collation (ending in _BIN2, not _BIN) to make sure
      -- that you don't skip differences that compare the same due to
      -- insensitivity of case, accent, etc, or linguistic equivalence.

      SET @Rows = @@ROWCOUNT;
  END;
END TRY
BEGIN CATCH
  RAISERROR(stuff);
  RETURN;
END CATCH;

Probably skip collate and the update is only one column.
I will search on maybe disabling the index if any , update and enable but I wanted to ask if this is a viable solution or if there is anything better?

Edit. Forgot to write that the updated rows would be at most a couple of hundred so I'm not sure if a batch makes a difference or not. Does the batch get ready the rows that will be searched for the update per pass of it just batch the number of rows to be updated to fit the BatchSize and the do the next rows found according to the BatchSize ?


Thanks.