Deleting Records from Large tables (Batch Delete)

Database maintenance is a necessary evil for a database administrator and deleting records is one of these necessary evils that we need to deal with. Deleting records can be tedious task especially in Very Large Databases.

Basic Delete

The easiest way to delete records from a database table is to use a DELETE FROM statement

DELETE FROM EMP WHERE EmpID = 'Value';

As the number of records grows, the amount of time it takes to delete records from our table increases. While the records are being deleted, the table is locked and other processes will be blocked until the completion of the delete statement.

Truncate Table

The truncate statement is a much faster delete process, which removes all records from a table.

TRUNCATE TABLE EMP;

A truncate statement is not always an option on a table, if the table has a foreign key relationship with another table, the table is used in an indexed view or used in replication, and the truncate option is not an option.

Batch Delete

With the limitation of the truncate statement and potential for blocking using the basic delete, that leaves us looking for an optimal solution for cleaning up records. Using a basic delete in conjunction with the TOP option and a loop, we can delete records in a batch.

DECLARE @BatchSize int  = 1000; WHILE EXISTS (SELECT 1 FROM EMP) BEGIN    DELETE TOP (@BatchSize) FROM EMP WHERE EMPID = 'Value'; END

Batch delete scrip that I have come across uses the ROWCOUNT as the factor for the loop.

DECLARE @BatchSize int  = 1000; -- Delete the initial batch outside the loop, this is needed to set @@ROWCONT DELETE TOP (@BatchSize) FROM EMP WHERE EmpID = 'Value'; WHILE (@@ROWCOUNT > 0) BEGIN    DELETE TOP (@BatchSize) FROM EMP WHERE EmpID = 'Value'; END