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
|
