Paul, this is good for something you really want to do 500 times. With a DELETE, what if there are only 100,000 rows to delete? You're going to execute the query 490 times for no reason... and if a scan is chosen to locate the rows to delete, this could be a pretty expensive no-op. I would rather do something like this (tailoring the TOP and % values depending on the size of the table and my tolerance for log pain):

http://twitpic.com/3rql7o

It's messier code, I admit, and I think the [GO int] is a fantastic and little-known gem, but I think a filtered delete is not a very practical use case.

Also, just as an aside, you didn't give @Timestamp a value. So actually you are going to execute this delete 500 times and never affect a single row...

If you put the above in a loop with
if @@ROWCOUNT = 0
BREAK

can you still use this trick?

Or you could do it the other way


SELECT *
INTO dbo.tmp_log
FROM dbo.log
WHERE timestamp >= @Timestamp

DROP TABLE dbo.log

exec sp_rename 'dbo.tmp_log', 'dbo.log'


... if there are no FKs to log table. You'll have to reindex it too.

I usually do it this way


That way it only loops until the table is empty


I had to run something like it against a big table...just be careful with enough log space...sometime it is faster to insert into another table what you want to keep, truncate this table and then insert the rows you want..if what you want to keep is much smaller

CREATE TABLE #test (id INT, somevalue VARCHAR(200) )
GO

--INSERT 2506 rows
INSERT #test
SELECT number, NEWID()
FROM master..spt_values

WHILE @@rowcount > 0
BEGIN
DELETE TOP (500) #test
END


---------------------
(500 row(s) affected)
(500 row(s) affected)
(500 row(s) affected)
(500 row(s) affected)
(500 row(s) affected)
(6 row(s) affected)
(0 row(s) affected)

Log space is definitely a hidden gotcha when doing huge deletes (truncates, too).

For some colossally-sized delete operation, if you don't want your log file to grow really large, batched deletes with interspersed log backups are the only way to prevent this.

If your database is in simple recovery mode, you don't even need the log backups, but you still need the batched deletes to keep the total log size small.

Using batched deletes is going to be faster, too. But truncate is fastest... but locks the table for the entire operation which also may not be right.

I agree with Denis to use WHILE @@Rowcount > 0 DELETE ...

Wow, I touched off a firestorm here. I know the code isn't 100% correct, I was really only trying to show the GO [int] statment. It was something I only just learned and I thought I would share it. WGW, your approach doesn't always work if the table needs to accessed by other processes in between your batch deletes.