Delete Large Number of Rows SQL Server

To delete rows without locking a table which has say more than  100 million rows to be deleted. Or a large table with archived data has to be deleted. You can use the below function to do this deletion.

 


DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT
SELECT @chunk_size=100000
SELECT @Count = 0
select @for_delete=count(*) from [Your big table] where [field name] > getdate () - 180

While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGIN TRAN
DELETE top(@chunk_size) FROM [Your big table] where [field name] > getdate () - 180

COMMIT TRAN
END

 

Advertisements