SQL Server: how to delete a table in chunks

 
I was asked the other day how someone should delete all of rows in a very large table when they couldn't use the TRUNCATE function because there were foreign keys tied to that table.

Deleting all the tables in a single step could cause a large transaction and cause lots of locks.

I found the code below a while back on a better way to minimize the maximum locks and resources on the server.  Deleting in smaller chunks creates a bunch of small transactions that is more manageable for the server to handle.

In addition, if you happen to have an error on one of the chunks, all the deletes up to then are retained and not rolled back, only that one chunk is rolled back.   Then again, if you have an error, all prior deletes are not recoverable.


For this example I'll create a table, populate it with a small set of tables, then the delete command is what we actually use to delete it in chunks.

--- Create table to hold data
CREATE TABLE #Cars (id int)

--- Add a number of Rows to table
DECLARE @x INT
SET @x = 1
WHILE(@x < 10000)
BEGIN
    INSERT INTO #Cars Select round((rand()*100),0)
    SET @x = @x + 1
END

   
--- Delete all the rows in chunks of 5000

--- If the number of ROWS processed in the DELETE statement are not Zero 

--the repeat the delete statement

DeleteThem:
    DELETE TOP(5000) FROM #Cars

if @@RowCount != 0
goto DeleteThem