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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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