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