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