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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to delete in small segments?

Execute the following T-SQL example script in Microsoft SQL Server Management Studio Query Editor to demonstrate large table delete in small segments without blocking other processes. Delete is a logged operation. Index B-Tree may get rearranged during DELETE which may cause significant slowdown.

-- SQL delete large number of records from a table
-- MSSQL select into table create for test
USE tempdb;
SELECT *
INTO   SOD
FROM   AdventureWorks2008.Sales.SalesOrderDetail
GO
--(121317 row(s) affected)
 
-- T-SQL delete loop - delete in segments - delete 2004 records
-- SQL table delete without blocking
WHILE (2 > 1)
  BEGIN
    DELETE TOP ( 10000 ) FROM SOD
    WHERE       YEAR(ModifiedDate) = 2004
    IF @@ROWCOUNT =-- no more to delete
      BREAK;
    -- 1 second delay loop to allow other processes to "jump in"
    WAITFOR delay '00:00:01'
  END
GO
 
/* Messages
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(5576 row(s) affected)
 
(0 row(s) affected)
*/
SELECT COUNT(* )
FROM   SOD
GO
-- 75741
 
-- Cleanup
DROP TABLE SOD

GO

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

Related article:

Delete large amount of data in sql server

 

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