Main | Stored Procedure to Restore SQL Database »

Best way to delete millions of rows SQL Server Stored Procedure

Need to delete millions of rows from a table in a SQL Server database?

I've often read the best way is to do 10,000 rows at a time by joining a key value on the target table to a key value in a TEMP table. Where 10,000 rows is just small enough that it doesn't get escalated to a table scan by the optimizer and incremental enough that if you have to kill your delete when business hours roll around you won't have to revert the whole shebang.

Anyway. I've ran into this enough that I've got a SPROC written up for it. You'll just need to provide:

  1. The target table name (duh).
  2. The key value (hopefully indexed)
  3. The # of rows (10K by default)
  4. Any additional WHERE clause criteria.

Hell. I even index the temp table for you. Note: if you don't have enterprise edition you won't be able to take advantage of the "WITH (DATA_COMPRESSION=PAGE)" so delete that.

Here's the SPROC:

You run it like this:

Keep in mind that if your key value is the clustered index column you may want to consider putting a nonclustered index on it. That way you're only pulling back one column in your JOIN/WHERE criteria. Rather than a clustered index's feature of having all columns in the leaf level.

Happy deleting!

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>