Search code examples
sqlsql-serverrowcount

SQL Server batch delete using WHILE loop does not work

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.

Solution

  • 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.