Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Deleting Large Number of Rows

Author  Topic 

Yak Posting Veteran

66 Posts

Posted - 2007-10-17 : 22:26:44

I'd like to know what is the best recommended and efficient method of deleting a very large number of rows (like 70,000 or more) from a table.

The first obvious thing I'd imagine would be to perform the delete operation on an indexed column but since a delete operation would write to the transaction logs, having enough allocated space on the disk where the transaction logs are would be another concern as well.

I have been told that to insert the records into a temp table would increase the performance and minimize the time required to perform the delete but I don't fully understand how this would work, if its the best method to use or how to do this correctly.

So my question is. What is the best method of deleting a large number of rows from a table and maximize the performance while minimizing the amount of time required to perform the operation?

Any professional suggestions would be most appreciated.

Starting Member

21 Posts

Posted - 2007-10-17 : 22:32:05
-- You could try this sort of thing.
-- It means you are actually committing, so any rollback options are gone - frequently thats okay for deletes (But be sure)
set rowcount 5000

while 1=1
begin tran
delete from X
if @@rowcount = 0

set rowcount 0
Go to Top of Page

Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-17 : 22:36:52
do it in batches.. use SET ROWCOUNT to limit the rows...

WHILE EXISTS ( SELECT * FROM table WHERE <condition to delete>)
DELETE Table WHERE <Condition>

You can also create a job to back up log with truncate only option and let it run every minute so your log file doesnt blow up..

Dinakar Nethi
Life is short. Enjoy it.
Go to Top of Page

Yak Posting Veteran

66 Posts

Posted - 2007-10-17 : 23:03:09
Wow! Thanks. I believe both responses are fairly the same in the general concept of performing the deletion by breaking the operation into batches. Dinakar's example I clearly understand and I will definitely try this; but could you provide a little more explanation for your sample Julien? It seems as though you are setting up an infinite loop by setting 1=1 Could you please explain this?

Delete from X, I believe or understand would be the actual delete statement with the where condition to constrain the number of rows to be deleted and a commit is performed after each row has been deleted until the @@rowcount variable reaches 0 indicating there aren't any more rows to delete based on the query. Is this correct?

If you could provide just a bit more of an explanation of your code, I'd like to test it as well.

Also one last question. Do either of you have a personal preference for the number of rows that should be included in a batch? 1000 or 5000?

Go to Top of Page


22859 Posts

Posted - 2007-10-18 : 00:03:43
Go to Top of Page

Yak Posting Veteran

66 Posts

Posted - 2007-10-18 : 05:52:18
I finally figured out the logic of the Julien.Crawford post and thanks for the provided link Kristen, it really helped provide closure for this.
Go to Top of Page

Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 06:31:32
Also, if you want to delete all the rows in a table then truncate it


Failing to plan is Planning to fail
Go to Top of Page

Starting Member

1 Post

Posted - 2007-11-07 : 09:47:24
I was looking at the ROWCOUNT documentation and BOL states:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

Something to keep in mind if your scripts will be run against sql 2008
Go to Top of Page


22859 Posts

Posted - 2007-11-07 : 09:55:31
As per the link I posted my preferred solution is to get all the PKs into a temp table and then delete in batches based on the Temp Table, so deprecation of SET ROCOUNT wouldn't effect that approach ... and DELETE TOP will do the same job of course (and much better since there won't be the potential side effect of Triggers only doing half a job!!)

Go to Top of Page

Not Just a Number

15586 Posts

Posted - 2007-11-07 : 10:16:11
SELECT * INTO WorkTable WHERE Stuff you'd like to keep
bcp out WorkTable
bcp in Main Table


What about RI?



Hint: Want your questions answered fast? Follow the direction in this link

Add yourself!

Go to Top of Page

- Advertisement -