Writing A Transact SQL (TSQL) Procedure For SQL Server 2008 To Delete Rows From Table Safely

CREATE PROCEDURE dbo.DeleteRowsInGroupsBeforeNdaysLog4NetAll
@NoOfRowsToDelete int,
@DaysBeforeTodayToDelete int
AS
BEGIN

  DECLARE @DateToDeleteBefore date    -- Number Of Days To Delete Records From
  DECLARE @DateToday datetime         -- To Hold Todays Date
  DECLARE @PrintMessage varchar(256)  -- To Hold Print Message        
  DECLARE @CurrentRowCount int        -- Scratch Var For Counting Rows
  
  SET @DateToday = GetDate()
  
  SELECT @CurrentRowCount =
         (
           SELECT COUNT(*)
           FROM dbo.Log4NetAll
           WHERE Date <(@DateToday - @DaysBeforeTodayToDelete)
         ) 
  
  WHILE (@CurrentRowCount > 0) BEGIN
      DELETE TOP (@NoOfRowsToDelete)
      FROM dbo.Log4NetAll
        WHERE Id IN (
        SELECT TOP (@NoOfRowsToDelete) Id 
        FROM Log4NetAll
        WHERE Date < (@DateToday - @DaysBeforeTodayToDelete)
        ORDER BY Id ASC)
      
       -- Count the records again
       SELECT @CurrentRowCount =
         (
           SELECT COUNT(*)
           FROM dbo.Log4NetAll
           WHERE Date <(@DateToday - @DaysBeforeTodayToDelete)
         ) 
       SET @PrintMessage = N'Deleting This Many Rows: '
                            + (CAST(@CurrentRowCount AS nvarchar(10)));
       PRINT @PrintMessage;
  END  
END

* Inspired by a blog post by Suprotim Agarwal

 

Basically, what is happening in the above code is first, we are testing to see if there are any records that meet our criteria to delete.  That is, records older than a certain date.  Next, we have a WHILE loop that continue until there are no more records that meet the criteria.  The DELETE SQL command uses TOP, which is very handy because it will delete up to that many.  That way, you don’t have to worry about deleting the last few records with any special case code.

 

The only thing a little tricky that I did was to actually delete the records with Subquery that forces a sort of the records before deleting.  That is, by asking for Id IN (Records sorted ascending), I’m forcing my TOP command to delete the oldest records first.  This may not be necessary if your method runs to completion, but if you interrupt it, it might be convenient for the oldest records to be deleted first.

DECLARE @NoOfRowsToDelete int;
DECLARE @DaysBeforeTodayToDelete int;

SET @NoOfRowsToDelete = 100;
SET @DaysBeforeTodayToDelete = 30;

EXEC [dbo].[DeleteRowsInGroupsBeforeNdaysLog4NetAll] 
  @NoOfRowsToDelete, @DaysBeforeTodayToDelete ;