A seated bearded man holding a hammer in his left hand while looking pensively at it, suggesting that he is tempted to use it to break the computer that is in front of him

Safely delete large number of records by using a loop

Published on

#programming #sys-admin #sql

I sometime need to delete up to millions of rows in our database and I can’t simply do a raw DELETE statement as such a huge query would mean having a very large transaction that would consume a lot if not all of the resources available. The simple alternative is to use an SQL loop, such as the following:

WHILE 1 = 1
BEGIN
   DELETE TOP(5000)
   FROM <table_name>
   WHERE <predicate>;
   IF @@ROWCOUNT < 5000 BREAK;
END

This way, each loop implicitly initiates its own transaction and each of those transactions has a reasonable size. However, remember that this is simply a quick and dirty trick, as there are way more efficient methods of deleting really large number of rows .

If you need to delete every row of a table, you should use the TRUNCATE statement instead since it is so much faster, like so:

TRUNCATE TABLE <table_name>