How to delete records from a large table in small portions

At times, you need to delete records from a large table. Performing a delete operation on such a table can become quiet resource intensive. In order to increase performance, you can delete the rows in small portions. Let us see how using this query:


-- Create a temporary table CustomersTemp
SELECT * into Northwind.dbo.CustomersTemp from
Northwind.dbo.Customers

-- Procedure to delete in small groups
CREATE PROC spoc_deleteinsmallgroups
@NoOfRowsToDelete int
AS
DECLARE @CurrentRowCount int

-- Count the number of rows in the table
SELECT @CurrentRowCount = (SELECT COUNT(*) FROM Northwind.dbo.CustomersTemp)

-- Loop and delete records in small bits till the rowcount is 0
WHILE(@CurrentRowCount > 0)
BEGIN
DELETE TOP (@NoOfRowsToDelete) FROM Northwind.dbo.CustomersTemp
-- RECOUNT
SELECT @CurrentRowCount = (SELECT COUNT(*) FROM Northwind.dbo.CustomersTemp)
Print @CurrentRowCount
END

-- Execute the procedure and pass the number of rows
-- to delete at a time
EXEC spoc_deleteinsmallgroups 25