What does this do?

Question

Once in a while, I need to clear out the anonymous user profiles from the database. A colleague has suggested I use this procedure because it allows a little breathing space from time to time for other procedures to run.

WHILE EXISTS (SELECT * FROM aspnet_users WITH (NOLOCK)  
    WHERE userID IN (SELECT UserID FROM #AspnetUsersToDelete))
BEGIN
  SET ROWCOUNT 1000
  DELETE FROM aspnet_users WHERE userID IN (SELECT UserID FROM #AspnetUsersToDelete )
  print 'aspnet_Users deleted: ' + CONVERT(varchar(255), @@ROWCOUNT) 
  SET ROWCOUNT 0
  WAITFOR DELAY '00:00:01'
END

This is the first time I've seen the NOLOCK keyword used and the logic for the rowcount seems backwards to me. Does anyone else use a similar sort of technique for providing windows in long running procedures and is this the best way of doing things?

Answer 1

Any time I anticipate deleting a very large number of rows, I'll do something similar to this to keep transaction batch sizes reasonable.

For SQL Server 2005+, you could use DELETE TOP (1000)... instead of the SET ROWCOUNT statements. I usually do:

SELECT NULL; /* Fudge @@ROWCOUNT value for first time in loop */

WHILE (@@ROWCOUNT <> 0) BEGIN
    DELETE TOP (1000)
    ...
END /* WHILE */