4

Currently on SQL Server 2008/R2. I just read here that "set rowcount" is being deprecated in some post 2012 release: http://technet.microsoft.com/en-us/library/ms188774.aspx

We have some legacy code that uses it presumably to delete small chunks of data and to minimize locking in our nightly "Data Purge" job.

Without rowcount, I would have to use "top xxx" instead?

  set rowcount 1000 /* Only delete this number of rows at a time */;
  declare @short_time datetime2 = DATEADD(week,-1,SYSUTCDATETIME());


      SET @TotalRowCount = 0 
      while ( select COUNT(1) from FlightAudit with (nolock) ) > @min_row_count
      begin
            delete FlightAudit where CreatedUTC < @short_time;
            SET @SaveRowCount = @@ROWCOUNT 
            SET @TotalRowCount = @TotalRowCount + @SaveRowCount 
            if @SaveRowCount=0 break;
            print 'delete from FuelerCertificationAudit...' + convert(varchar(10),@SaveRowCount) + ' Time=' + dbo.DateOrNullToChar(getdate()) 
      end
      print 'Total deleted from FlightAudit...' + convert(varchar(10),@TotalRowCount) + ' Time=' + dbo.DateOrNullToChar(getdate()) 
7

Yes. Just remove the set rowcount and use

DELETE TOP (1000) FlightAudit
WHERE  CreatedUTC < @short_time; 

The rest of the code can remain unchanged.

This deletes an arbitrary 1000 rows matching the WHERE clause (same semantics as the original query).

If you wanted to define a particular ORDER BY for the TOP then you could use

WITH T
     AS (SELECT TOP (1000) *
         FROM   FlightAudit
         WHERE  CreatedUTC < @short_time
         ORDER  BY CreatedUTC)
DELETE FROM T; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.