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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s