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 29 million rows of a table?

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate large update in small batches with waitfor delay to prevent blocking.


-- T-SQL large update table
USE tempdb;
SELECT * INTO SOD
FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
--(121317 row(s) affected)
 
-- SQL update in batches of 10,000
WHILE (2 > 1)
  BEGIN
    BEGIN TRANSACTION
    UPDATE TOP ( 10000 ) SOD
    SET    UnitPriceDiscount = 0.08,
           ModifiedDate = CONVERT(DATETIME,CONVERT(CHAR(10),getdate(),112))
    WHERE  ModifiedDate < CONVERT(DATETIME,CONVERT(CHAR(10),getdate(),112))
    
    IF @@ROWCOUNT = 0
      BEGIN
        COMMIT TRANSACTION
         BREAK
      END
    COMMIT TRANSACTION
    -- 1 second delay
    WAITFOR DELAY '00:00:01'
  END -- WHILE
GO
 
/* Messages
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(1317 row(s) affected)
 
(0 row(s) affected)
*/
-- Cleanup
DROP TABLE SOD
GO

------------

Related articles:

What is the recommended batch size for SqlBulkCopy?

Batch Update on a very huge table

How to Use Batch Size to Speed Mass Updates, Inserts and Deletes

UPDATE (Transact-SQL)

http://www.sqlusa.com/bestpractices/largeupdate/

 

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