Updating millions of rows from SQL Server

So, you have a large table where you need to update every row, using some condition.

If you run a:

UPDATE SOMETABLE SET NAME = 'FRED'
WHERE "SOME_CONDITION"

...you'll be waiting a long time. The table will be locked, the transaction space will grow and grow, and you won't know if it will ever finish until it does... or it doesn't.

One "trick" that people talk about is turning off indexes, so they don't have to be updated. But while that's effective for inserts, it struggles in this situation: because you end up having to do table scans to find which rows to update. So don't turn off indexes: double-down on indexes. Make sure the query you're using is able to efficiently find the records it wants to update.

Some people say: "select the table into a new, transformed table, then drop the old table and rename the new." That will double your space requirements and I think it can eradicate your statistics too, so I don't really go in for that.

What I like is the little loopy methods with update top(n), similar (almost identical, natch) to the technique I use for Deleting millions of rows from SQL Server.

This example uses Update top(@batchsize) ... in a while loop to do the needful. It means you'll get a sense of how long it will take... and if it gets interrupted it can resume.

DECLARE
		@BATCHSIZE INT,
		@WAITFORVAL VARCHAR(8),
		@ITERATION INT,
		@TOTALROWS INT,
		@MAXRUNTIME VARCHAR(8),
		@BSTOPATMAXTIME BIT,
		@MSG VARCHAR(500),
		@STARTALL DATETIME,
		@STARTBATCH DATETIME,
		@ENDBATCH DATETIME

SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000;
SET @WAITFORVAL = '00:00:10' -- Delay between updates **** UPDATE THIS ****
SET @MAXRUNTIME = '08:00:00' -- 8AM
SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS

SET @STARTALL = GetDate()

WHILE @BATCHSIZE>0
BEGIN
		-- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
		IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1
		BEGIN
				RETURN
		END

		SET @STARTBATCH = GetDate()

		/* vv You need to update this! ***** */
		UPDATE TOP(@BATCHSIZE)
SET Column1 = 'Value'
		FROM SOMETABLE
		WHERE SOMECONDITION
		/* ^^ You need to update that ***** */

		SET @BATCHSIZE=@@ROWCOUNT
		SET @ENDBATCH = GETDATE()

		SET @ITERATION=@ITERATION+1
		SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
		SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
		RAISERROR (@MSG, 0, 1) WITH NOWAIT

		SET @MSG = 'Batch duration: ' +
						Cast(DATEDIFF(s, @STARTBATCH, @ENDBATCH) as varchar) +
						' seconds. Batch Speed ' + CAST(CAST(@BATCHSIZE as float)/(DATEDIFF(s, @STARTBATCH, @ENDBATCH)) AS VARCHAR) +
						' rows per seconds. Subtotal elapsed: ' +
						CAST(DATEDIFF(s, @STARTALL, @ENDBATCH) as varchar) +
						' seconds. Cumulative Speed ' + CAST(CAST(@TOTALROWS as float)/(DATEDIFF(s, @STARTALL, @ENDBATCH)) AS VARCHAR) +
						' rows per second'
		RAISERROR (@MSG, 0, 1) WITH NOWAIT

		WAITFOR DELAY @WAITFORVAL  -- ** DECIDE IF YOU WANT THIS BREATHING-ROOM DELAY ON EACH LOOP....
END

SET @MSG = 'TOTAL elapsed: ' +
	CAST(DATEDIFF(s, @STARTALL, @ENDBATCH) as varchar) +
	' seconds. Total rows: ' + CAST(@TOTALROWS AS VARCHAR) +
	'. Final Speed: ' + CAST(CAST(@TOTALROWS as float)/(DATEDIFF(s, @STARTALL, @ENDBATCH)) AS VARCHAR) +
	' rows per second'
RAISERROR (@MSG, 0, 1) WITH NOWAIT

See also