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.

Answer 1

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 

Answer 2

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.