Mark Mintoff My superpower is common sense

13Mar/120

T-SQL Batch Delete

There are times when you need to delete an arbitrarily large subset of data from a database table. For the sake of example, let us suppose we will be deleting 100,000,000 rows. A simple delete statement:

will take a very long time (hours) to process as 100,000,000 rows will be deleted within a single transaction.

For this purpose, when I need to delete a large subset of data, I prefer to work with a while loop delete and multiple transactions:

This results in a much faster deletion of data as each transaction will only need to delete 1000 rows at a time. An additional benefit is presented if you need to stop the deletion at any point; for example you have locked the table and somebody needs to select some data. There are no long rollbacks and you do not lose progress on what you have deleted.

Hence, to make things easier:

Alternatively:

Enjoy!

VN:F [1.9.22_1171]
Rating: 4.7/5 (3 votes cast)