Objective: Delete rows older a than a certain date/time (age in hours)
Assumptions: The row ids are in ascending order of their creation date
Algorithm:
Get the lowest_id in the table.
SELECT TOP 1 Id From table with(nolock) order by id asc option(maxdop 1)
Get upper_id for the batch by adding the batch size to the lowest_id.
SELECT top 1 id from table with(nolock) where id >= lowestId and id < lowestid+batchcount order by id DESC option (MAXDOP 1)
Get the date_created for the row with upper_id.
SELECT date_created from tablename with(nolock) where id=upper_id option (MAXDOP 1)
If the upperend_date_created older than the specified number of hours, delete all rows below the upper_id .
IF upperend_date_created <= DATEADD(HOUR, - hours, GETUTCDATE())
delete from table where id <= upperendid option (maxdop 1)
