--===== If the test table exists, drop it
     IF OBJECT_ID('dbo.JBMTestDetail','U') IS NOT NULL
        DROP TABLE dbo.JBMTestDetail
GO
--===== Create and populate a 1,000,000 row test table.
     -- Column "ConnID" has a range of 1 to 100,000 non-unique numbers stored as VARCHAR(30)
     -- Column "Key1" has a range of 1 to 100,000 non-unique numbers stored as VARCHAR(30)
     -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
     -- Column "Time_Stamp" has a range of  >=01/01/2005 and <01/01/2015 non-unique date/times
     -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
     --        for all rows.
     -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

 SELECT TOP 1000000
        ConnID       = ISNULL(CAST(ABS(CHECKSUM(NEWID()))%100000+1 AS VARCHAR(30)),''), --(10 rows per connection)
        Key1         = ISNULL(CAST(ABS(CHECKSUM(NEWID()))%100000+1 AS VARCHAR(20)),''), --just to test index with
        SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%26+65),
        SomeCSV      = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
        SomeMoney    = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
        Time_Stamp   = ISNULL(CAST(RAND(CHECKSUM(NEWID()))*3652.0+38351.0 AS DATETIME),0),
        SomeHex12    = RIGHT(NEWID(),12)
   INTO dbo.JBMTestDetail
   FROM Master.dbo.SysColumns t1,
        Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== Create indexes similar to Troy's
 CREATE    CLUSTERED INDEX IXC_JBMTestDetail_Time_Stamp_ConnID ON dbo.JBMTestDetail (Time_Stamp,ConnID)
 CREATE NONCLUSTERED INDEX IX_JBMTestDetail_ConnID_Key1        ON dbo.JBMTestDetail (ConnID,Key1)
GO

--===== Setup to measure performance...
SET STATISTICS TIME ON

--========================================================================
--      Demo the delete crawler on the test table constructed above
--========================================================================

--===== Define the cutoff date with a time of "midnight" or, if you will,
     -- define the cutoff date with no time so we only delete whole days.
DECLARE @CutoffDate DATETIME
 SELECT @CutoffDate = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-94)

--===== Limit all further queries, including deletes, to 25,000 rows
     -- (about 1 second worth of deletes, like I said before)
    SET ROWCOUNT 25000

--===== See if any rows qualify for deletion.  If even just one exists,
     -- then there's work to do and @@ROWCOUNT will be > 0.
     -- DO NOT PUT ANY CODE BETWEEN THIS SELECT AND THE WHILE LOOP OR
     -- YOU'LL MESS UP THE ROWCOUNT FOR THE WHILE LOOP
 SELECT TOP 1 1 FROM dbo.JBMTestDetail WHERE Time_Stamp < @CutoffDate

--===== If the rowcount from the above is greater than 0,
     -- then delete 25,000 rows at a time until there's nothing
     -- left to delete
  WHILE @@ROWCOUNT > 0
  BEGIN 
        --===== Just a "marker" to separate the loop in the output
          PRINT REPLICATE('=',78) 

        --===== This delay gives other processes breathing room 
        WAITFOR DELAY '00:00:10' 

        --===== Do the delete. Will be limited by the SET ROWCOUNT above.
             -- DO NOT PUT ANY CODE BETWEEN THIS DELETE AND THE "END" OR 
             -- YOU'LL MESS UP THE ROWCOUNT FOR THE WHILE LOOP.
         DELETE dbo.JBMTestDetail WITH (TABLOCKX)
          WHERE Time_Stamp < @CutoffDate
    END

--===== Restore the ability to process more than 25,000 rows
    SET ROWCOUNT 0

     Jul 13  

Sometimes this is me

     Jul 13  

Sometimes this is me