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