Deleting Batches of Rows with TOP

A previous article on how to use TOP by Andy Warren provided some useful ideas on deleting rows in batches, which provides nice availability and logging benefits. This article expounds on his a little bit.

Specifically, when using DELETE TOP (N), what happens if you have more than N rows that match your criterion? It's an easy problem, but here's some procedural code for you to use as a starting point.

For larger tables, where this technique is most effective, you would obviously use larger values for @N and probably constrain on a date column.

(Aside: To those of you who hesitate to contribute articles because you worry about writing something useless or duplicating existing material, this article is a perfect example of why you should reconsider; someone out there might find this simple variation on an already-addressed topic useful!)

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.