SQL Server batch delete using WHILE loop does not work

Question

I have a very large table, so I am using the following to delete older entries:

WHILE (@@ROWCOUNT > 0)
BEGIN
    DELETE TOP (5000) FROM myTable
    WHERE date < 20130103
END

I have run this a few times using different dates. Sometimes it works fine (takes about 20 minutes), but other times the query finishes immediately and nothing has been deleted. When that happens, I just do a simple SELECT statement from that table, and then try the above WHILE statement again, and then it works! Anyone know why this is? I need to automate this query to run on a regular basis to control the table size, but I want to make sure it actually deletes properly when it runs. Thank you.

Shouldn't the date be written as `'2013-01-03'` rather than as a number? — Andrew Morton, Mar 21 '13 at 21:10
Also note that just having a loop doesn't necessarily reduce the impact to the log or concurrency depending on whether this is a single transaction. I would stop using @@ROWCOUNT for control, add transactions inside the loop, and set a variable = @@ROWCOUNT. See http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletes — Aaron Bertrand, Mar 21 '13 at 21:37
Incidentally @AndrewMorton, SQL Server does understand '20130103' to be the same as '2013-01-03' (when doing boolean logic with dates), but yes the OP would have to put quotes around the number for it to be treated as a date. Makes you wonder how the OP (and others below) tested the query because I get the following error when I try the posted syntax: "Arithmetic overflow error converting expression to data type datetime"? — Andrew Jens, Jul 20 '17 at 23:49
@AndrewJens [Be Wary of Date Formatting in T-SQL](https://bornsql.ca/2017/07/wary-date-formatting-t-sql/) just came to my attention via the SQL Server Central newsletter. It looks like the format without the dashes is safer than with. — Andrew Morton, Jul 21 '17 at 09:04

Michael Fredrickson · Answer 1 · 2013-03-21T21:24:07.507

54

What are you running before this block of code? @@ROWCOUNT will be set to whatever statement proceeded it.. if you run some other command beforehand, it could be 0.

Instead, you could force the initial count to be 1:

DECLARE @Rows INT
SET @Rows = 1

WHILE (@Rows > 0)
BEGIN
    DELETE TOP (5000) FROM myTable
    WHERE date < 20130103

    SET @Rows = @@ROWCOUNT
END

edited Mar 21 '13 at 21:24

answered Mar 21 '13 at 21:09

Michael Fredrickson

36,839
5
92
109

score 12 · Accepted Answer · answered Mar 21 '13 at 21:08

12

Presumably, the reason is because @@ROWCOUNT is initialized to a value of 0.

You could run this query first to set it:

select count(*) from myTable where date < 20130103

This would add a little bit of time to your query, but you would see the number of rows being deleted.

You could also do something like:

select top 1     * from myTable

which would go much faster.

answered Mar 21 '13 at 21:08

Gordon Linoff

1,242,037
58
646
786

18

You could also just `SELECT 1;` before the loop. – Aaron Bertrand Mar 21 '13 at 21:33

Eduardo Gelvis · Answer 3 · 2018-09-06T22:30:20.577

When I delete in batch I add a WAITFOR DELAY (at least 1 or 2 seconds). Also the first statement I created outside the loop. Btw, avoid using ROWCOUNT as delimiter (https://learn.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-2017). There are the two options:

DECLARE @BatchSize BIGINT = 50000
SET ROWCOUNT @BatchSize

DELETE 
FROM myTable 
WHERE 
date < 20130103

WHILE (@@ROWCOUNT > 0)
BEGIN
WAITFOR DELAY '00:00:02'
DELETE 
FROM myTable 
WHERE 
date < 20130103
END

Or

DECLARE @BatchSize BIGINT = 50000

WHILE 1=1
BEGIN

    WAITFOR DELAY '00:00:02'

    DELETE TOP(@BatchSize )
    FROM myTable 
    WHERE 
    date < 20130103

    IF @@ROWCOUNT < @BatchSize 
        Break
END

score 6 · Answer 4 · answered Mar 21 '13 at 21:11

6

It's because sometimes @@ROWCOUNT is zero to start with - so the while loop never executes, because it checks the condition before every execution, including the first one.

Here's a homemade do-while loop, since SQL Server doesn't have one built in.

loop:
   DELETE TOP (5000) FROM myTable
   WHERE date < 20130103
if @@ROWCOUNT > 0 goto loop

answered Mar 21 '13 at 21:11

Blorgbeard

101,031
48
228
272

1

SQL Server doesn't have a [`WHILE`loop](http://msdn.microsoft.com/en-us/library/ms178642(v=sql.100).aspx)? – Aaron Bertrand Mar 21 '13 at 21:34
6

@AaronBertrand he is talking about a `do-while` loop. As in: `DO BEGIN END WHILE ` (always runs the "" once before checking the condition). THAT doesn't exist in SQL Server. – EvilBob22 Mar 21 '13 at 22:06

score 2 · Answer 5 · answered Aug 23 '17 at 11:14

Basically,

SELECT 0 -- rowcount is 1 
WHILE (@@ROWCOUNT > 0)
BEGIN
    DELETE TOP (5000) FROM myTable
    WHERE date < 20130103
END

Or

SET ROWCOUNT 5000 -- set row count to 5000
SELECT 0 -- rowcount is 1 
WHILE (@@ROWCOUNT > 0)
BEGIN
    DELETE FROM myTable
    WHERE date < 20130103
END
SET ROWCOUNT 0  -- set rowcount to unlimited

score 1 · Answer 6 · answered Jun 18 '13 at 19:02

1

You could also write your query this way:

SET ROWCOUNT 5000; -- set batch size
WHILE EXISTS (SELECT 1 FROM myTable WHERE date < '2013-01-03')
BEGIN
    DELETE FROM myTable
    WHERE date < '2013-01-03'
END;
SET ROWCOUNT 0; -- set batch size back to "no limit"

Either way, you should format your date strings properly.

Just be sure your delete criteria and the statement in your exists clause are identical or you could encounter an infinite loop.

answered Jun 18 '13 at 19:02

Jim Clouse

8,774
6
32
25

4

Be aware that after SQL 2012: "Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax." – Jim Clouse Dec 29 '16 at 14:24

Luiz Fernando · Answer 7 · 2023-06-05T13:09:08.553

I have upgraded this loop delete adding a while function into another while function. This helps me deleting massive rows an very very large table (more than 700gb) without an index.

declare @dtini as date = '20220101'
declare @dtfin as date = '20220228'
DECLARE @Rows INT

while (@dtini <= @dtfin)
begin
    SET @Rows = 1
    WHILE (@Rows > 0)
    BEGIN
        DELETE TOP (100000) FROM mytable
        WHERE [date] = @dtini 
        SET @Rows = @@ROWCOUNT
        print @dtini
    END

set @dtini = DATEADD(DAY,1,@dtini)

end

SQL Server batch delete using WHILE loop does not work

7 Answers7