SQL Server batch delete using WHILE loop does not work

23
votes

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.

6
sqlsql-serverrowcount
Shouldn't the date be written as '2013-01-03' rather than as a number? - Andrew Morton
Funny that every answer totally missed that. - Aaron Bertrand
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 sqlperformance.com/2013/03/io-subsystem/chunk-deletes - Aaron Bertrand
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
@AndrewJens Be Wary of Date Formatting in 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

6 Answers

50
votes

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
11
votes

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.

6
votes

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
5
votes

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://docs.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
2
votes

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
1
votes

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.