T-SQL: Delete Rows in a Batch Loop

Here’s a simple pattern to perform an action in a batch loop with T-SQL.

DECLARE @RowCount INT = 1; -- Initialize to a value >0 to begin the loop
 
SET ROWCOUNT 20000; -- The number of rows to delete in a batch
 
WHILE @RowCount > 0
BEGIN
    PRINT 'Deleting...'
 
    DELETE dbo.BigTable WHERE SomeDate < '1970-01-01'; 
 
    SET @RowCount = @@ROWCOUNT;
 
    CHECKPOINT; -- Generates a manual checkpoint in the SQL Server database (see https://msdn.microsoft.com/en-us/library/ms188748.aspx)
 
    WAITFOR DELAY '00:05'; -- Pause for 5 seconds between each batch
END
This entry was posted in MS SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *