SQL Steve’s SQL Tips - While replacement

Instead of counters and while loops, this is a simple replacement.

But what if you have billions of rows in the “ErrorLog” table and you need to delete them a batch at a time so that you don’t lock up the entire table? You can use the “TOP” operator to specify the batch size and then specify an integer value that follows the “GO” keyword specifying the number of batches to execute.

For example:

DELETE
TOP(100000)

FROM

    ErrorLog

WHERE

    ErrorTime < ‘2008-01-01′

GO 10000

SQL Steve’s SQL Tips