Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Deleting Batches of Rows with TOP

By Ted Manasa,

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: 10586 | Views in the last 30 days: 7
 
Related Articles
FORUM

Insert / Deletes

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    
 
Contribute

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.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones