There are more than few times I have had to delete a lot of data — not trucate just delete a lot based upon where clause, this is to create test databases, reload data etc
DELETING every 1000 rows not only makes it faster, but in case you want to cancel the query that can happen quickly too as your transaction logs aren’t going to grow
1 |
SELECT 1 |
2 |
WHILE @@ROWCOUNT > 0 |
3 |
BEGIN |
4 |
DELETE TOP (1000) |
5 |
FROM LargeTable |
6 |
END |
My Favorite SQL 