Gradually Deleting Data in SQL Server
This week, I found a very interesting tip from Glenn Berry explaining how to deal with large delete operations. If you have a situation where you have a very large table in SQL Server, where you need to periodically delete millions of rows, there are several ways to do it. If you have a maintenance window (or your database is not required to be available 24 x 7 x 365), you can just delete all of the rows in one shot, using a set based operation. This would be the quickest way to delete a large number of rows, but you will probably end up getting lock escalation to a table lock, which essentially makes the table unavailable during the delete. A safer, but much more time consuming way to delete millions of rows is to use some sort of looping mechanism, where you gradually delete a fairly small number of rows in a loop, to slowly nibble away at the table. This will take much longer than a set based operation, but, if done properly, will not cause concurrency problems. Here is a short s