SQL Script - two delete statements

Question

I found this code into our repository and I need to modify the statement. However, I am bit hesitant because I am wondering, why does it have two delete statements? I've tried to debug it, remove the first delete, it didnt work. I understand that it will run the delete per 100 rows. Is there an option that I can have the same behavior but only have one DELETE statement? TIA!

USE [DBQ] 

SET ROWCOUNT 100
DELETE FROM dbo.setting WITH(ROWLOCK) WHERE CreateDate < DATEADD(day, -90, GETDATE())

WHILE @@rowcount > 0
BEGIN
 SET ROWCOUNT 100
 DELETE FROM  dbo.setting WITH(ROWLOCK) WHERE CreateDate < DATEADD(day, -90, GETDATE())
END

Re-tagged with `tsql` and `sql-server` based on the non-standard SQL used — , Jun 11 '18 at 10:30

score 1 · Answer 1 · answered Jun 11 '18 at 10:27

You can simply remove the rowcount:

DELETE FROM dbo.setting WITH(ROWLOCK)
    WHERE CreateDate < DATEADD(day, -90, GETDATE());

Note this from the documentation:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. For more information, see TOP (Transact-SQL).

The reason the code would be written this way is to batch the deletes -- deleting a lot of records can take a long time.

If you want to maintain the batch deletes, then do something like:

declare @num_deleted int;
set @num_deleted = 999;

WHILE (@num_deleted > 0)
BEGIN 
     DELETE todelete
     FROM (SELECT TOP (100) s.*
           FROM dbo.setting s WITH(ROWLOCK)
           WHERE CreateDate < DATEADD(day, -90, GETDATE())
          ) todelete;

     set @num_deleted = @@rowcount;
END;

score 0 · Answer 2 · answered Jun 11 '18 at 11:00

0

You can just do a fake select to get a @@rowcount

select 1 
WHILE @@rowcount > 0
BEGIN
  DELETE top (100) 
  FROM  dbo.setting WITH(ROWLOCK) 
  WHERE CreateDate < DATEADD(day, -90, GETDATE())
END

answered Jun 11 '18 at 11:00

paparazzo

44,497
23
105
176

But that will send a resultset back to the executing process - which may not be desirable. – SMor Jun 11 '18 at 11:12

score 0 · Answer 3 · answered Jun 11 '18 at 14:35

0

Add a variable and change its value inside the loop.

declare @Start bit = 0
WHILE (@Start = 0 OR @@rowcount > 0)
BEGIN
  Set @Start = 1

  DELETE top (100) 
  FROM  dbo.setting WITH(ROWLOCK) 
  WHERE CreateDate < DATEADD(day, -90, GETDATE())
END

answered Jun 11 '18 at 14:35

Pratheek

71
5

SQL Script - two delete statements

3 Answers3