Sometimes we are required to do mass database UPDATES. On very large tables this can become a problem. For example, I recently had a table with 80 million records in it which required a mass UPDATE. Instead of running one large UPDATE statement it is better to break the transactions into smaller batches. This will reduce the locking incurred on the table in question.
WHILE (2 > 1) BEGIN BEGIN TRANSACTION UPDATE TOP(1000) dbo.tbl_shipments SET ship_zone = 99999 WHERE ship_zone = 0; IF @@ROWCOUNT = 0 BEGIN COMMIT TRANSACTION BREAK END COMMIT TRANSACTION -- 1 second delay WAITFOR DELAY '00:00:00.200' END -- WHILE GO
There is a really good article at http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes which goes into depth about the issue. Although this article is focused on deletes it also applies to UPDATE statements.