How to delete records in batches while reducing locking

I have used the technique below to delete rows in batches which includes pauses to let other process access the table. USE IT AT YOUR OWN RISK!! I take NO responsibility if you run it on your own system - in fact that goes for any of the code on my blog.

DECLARE @MaxID INT

DECLARE @MinID INT

DECLARE @Date DATETIME

DECLARE @MyTableVar TABLE (ID INT)

SET @Date = GETDATE()

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#DelRows'))

    DROP TABLE #DelRows

CREATE TABLE #DelRows (ID INT)

/*

To try and reduce table scans on large tables we get the minimum and maximum ID (assuming ID is a Clustered Index) of the date range and use those as the minimum and maximum criteria for the filter

*/

-- Find latest ID in range

SET @MaxID = (

              SELECT TOP 1 t1.Table1_ID

              FROM dbo.Table1 t1 WITH (NOLOCK)        

              WHERE t1.TableDate < DATEADD(HOUR, - 24, @date)

              ORDER BY t1.TableDate DESC

              )

-- Find earliest ID in range

SET @MinID = (

              SELECT TOP 1 t1.Table1_ID

              FROM dbo.Table1 t1 WITH (NOLOCK)

              WHERE t1.TableDate >= @Date

              ORDER BY t1.TableDate ASC

              )

-- Insert the records to delete into a temporary table

INSERT #DelRows

SELECT TOP 500000 ID

FROM Table1 t1(NOLOCK)

WHERE t1.Table1_ID BETWEEN @MinID

              AND @MaxID

-- Delete the records in batches of 1000

WHILE 1 = 1

BEGIN

       DELETE TOP (1000) t1

       OUTPUT DELETED.ID

       INTO @MyTableVar

       FROM Table1 t1

       JOIN #DelRows d ON t1.ID = d.ID

       DELETE d2

       FROM #DelRows d2

       JOIN @MyTableVar m ON d2.ID = m.ID

       DELETE @MyTableVar

      

-- We put in a delay to give other queries a chance

       WAITFOR DELAY '000:00:05'

END