DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to update large table with millions of rows?

The large update has to be broken down to small batches, like 10,000, at a time. This allows normal operation for the server. It is also easy to restart in case of interruption. WAITFOR DELAY can be included to slow down the batch processing.

-- T-SQL using the TOP function (SQL Server 2005 and on ) to limit UPDATE size

USE Northwind

 

WHILE (1 = 1)

BEGIN

    BEGIN TRANSACTION

    

    UPDATE TOP (1000) [Order Details]

    SET    Discount = 0.1

    WHERE  Discount != 0.1 -- predicate for filtering;

    

    IF @@ROWCOUNT = 0 -- terminating condition;

      BEGIN

        COMMIT TRANSACTION

        BREAK

      END

    

    COMMIT TRANSACTION

-- WAITFOR DELAY '00:00:01';

END

 

 

 

 

-- T-SQL using the ROWCOUNT setting to control update size

USE Northwind

 

SET ROWCOUNT  1000

 

WHILE (1 = 1)

  BEGIN

    BEGIN TRANSACTION

    

    UPDATE [Order Details]

    SET    Discount = 0.11

    WHERE  Discount != 0.11

    

    IF @@ROWCOUNT = 0

      BEGIN

        COMMIT TRANSACTION

        

        BREAK

      END

    

    COMMIT TRANSACTION

  END

 

SET ROWCOUNT  0

 

Related link:

High Volume Update Performance

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE