Home Forums SQL Server 2008 T-SQL (SS2K8) Why is this code deleting only 1 row at a time instead of using the condition and deleting many rows? RE: Why is this code deleting only 1 row at a time instead of using the condition and deleting many rows?

  • Assuming this process is run once a day and you want current day plus last 90 days, give this a try:

    declare @BatchSize int = 10000,

    @CurrentDate date = getdate(),

    @KeepDays int = 90;

    while @BatchSize > 0

    begin

    delete top (@BatchSize)

    from dbo.ServiceLog

    where

    Created < dateadd(day,-(@KeepDays),@CurrentDate);

    set @BatchSize = @@ROWCOUNT;

    end