# Deleting Batches of Rows with TOP

By Ted Manasa, 2008/07/03

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.

```CREATE TABLE tab1
( col1 INT
, col2 CHAR(1)
)

INSERT INTO tab1 VALUES (1, 'A')
INSERT INTO tab1 VALUES (1, 'B')
INSERT INTO tab1 VALUES (1, 'C')
INSERT INTO tab1 VALUES (1, 'D')
INSERT INTO tab1 VALUES (1, 'E')
INSERT INTO tab1 VALUES (8, 'F')
INSERT INTO tab1 VALUES (9, 'G')

----------------------------

BEGIN

DECLARE @N INT -- Number of rows to delete per batch
DECLARE @cnt decimal(18,2) -- Total count of rows matching specified criterion
DECLARE @loops INT -- Number of times the DELETE statement should loop to delete all relevent records

SET @N = 2

SELECT @cnt = COUNT(*) FROM tab1 WHERE col1 = 1

SET @loops = CEILING(@cnt/@N)

WHILE @loops > 0
BEGIN
DELETE TOP (@N) FROM tab1 WHERE col1 = 1
SET @loops = @loops - 1
END

END

-----------------

SELECT * FROM tab1

DROP TABLE tab1
```

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!)

Total article views: 10504 | Views in the last 30 days: 16

Related Articles
FORUM

Insert / Deletes

FORUM

### Optimizing inserts and deletes on a single large table

Optimizing inserts and deletes

ARTICLE

### Dynamic creation of Insert, Update, Delete Stored procedures

Automates the creation of INSERT, UPDATE & DELETE stored procedure of a table

ARTICLE

### Automated Trigger To Require a WHERE Clause

Learn how you can build triggers that prevent an update or delete statement from being run without a...

FORUM

### Trigger that uses a Parameter with Inserted/Deleted

Trigger that uses a Parameter with Inserted/Deleted

Tags
 delete top t-sql

## Join the most active online SQL Server Community

### SQL knowledge, delivered daily, free:

#### 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.