Main | Stored Procedure to Restore SQL Database »
Friday
Dec082017

SQL Stored Procedure to Delete Lots of Rows

The best way I've found to delete a monstrous amount (say millions) of rows is to create temp table containing a SARGable key value that's indexed on the table.  Join that to your target table and delete, say, 10,000 rows at a time.  Where 10,000 rows is a value that the optimizer doesn't escalate to a table scan.

 

Don't forget that if you're only checking one key value you may want to create a compressed nonclustered index on this value in the target table even if that column is already your clustered index.  Reason being you may not want to pull back every column in the table which the clustered index will have in its leaf level.

 

Anyway.  Take the SPROC for a spin and let me know what you think.  It's right here.

PrintView Printer Friendly Version

EmailEmail Article to Friend

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):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>