Deleting batches of rows with TOP

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ted Pin » Deleting batches of rows with TOP

46 posts, Page 1 of 5

1 2 3 4 5 »»»

Deleting batches of rows with TOP

Rate Topic

Display Mode

Topic Options

Author

Message

Ted Manasa

Posted Wednesday, July 02, 2008 11:02 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 1:33 PM
Points: 85, Visits: 299

Comments posted to this topic are about the item Deleting batches of rows with TOP

---------------------------
|Ted Pin >>

Post #527756

david.gerrard-604066

Posted Thursday, July 03, 2008 1:21 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:48 AM
Points: 17, Visits: 63

I'm probably missing something here:

Why does that example do anything different to:

DELETE FROM tab1 WHERE col1 = 1;

???

Post #527810

David McKinney

Posted Thursday, July 03, 2008 1:24 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:52 AM
Points: 582, Visits: 1,603

I certainly wouldn't call this an article. I found the "aside" most amusing - and is almost as long as the so called article.

If you are hesitating about writing an article (as mentioned in the aside) then I'd recommend you come up with something more than a paragraph, where the purpose of your article and your intention is clearly stated - where you instruct or enlighten the reader, and finally where you draw some sort of conclusion.

If you can't do any of the above, then perhaps hesitate some more until you can.

David McKinney.

Post #527812

WilliamD-

Posted Thursday, July 03, 2008 1:32 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:34 AM
Points: 449, Visits: 1,772

Hi david.gerard,

the idea is that it can be much better to delete in batches when handling a much larger data set than in the example. Imagine you have to do a delete of 5 million entries in a 20 million entry table that is in a production system. You would have some (necessary) blocking which could seriously slow down your production system.

As a note to Ted Pin - Why is the variable @cnt a decimal and not integer? You cannot get 2.5 rows back with your count(*) query.

Also, would this not be even better using a numbers table á la Jeff Moden http://www.sqlservercentral.com/articles/TSQL/62867/ ?

Regards

GermanDBA

Regards,

WilliamD

Post #527815

david.gerrard-604066

Posted Thursday, July 03, 2008 1:34 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:48 AM
Points: 17, Visits: 63

There's no need to get all 'Comic Book Guy' about this. The example seems a bit muddled, but I thought the aside was actually quite a well made point.

The one thing that is likely to put people off posting articles is when people get snippy about them. And if you don't appreciate having your time wasted by 'unenlightening articles', why reply in the first place?

Post #527818

david.gerrard-604066

Posted Thursday, July 03, 2008 1:36 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:48 AM
Points: 17, Visits: 63

I see. (I thought there would probably be a reason in there somewhere). Thanks.

Post #527820

Christoph D

Posted Thursday, July 03, 2008 1:58 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, December 28, 2012 7:04 AM
Points: 227, Visits: 262

I am wondering, why not use SET ROWCOUNT ?

Normally I am using rowcount because it is really easy to handle.

Set Rowcount @n

select 1

While @@ROWCOUNT > 0
BEGIN
Delete from .... where x = y
END

Should do the same without too much calculation and too many variables Hehe

Post #527828

David McKinney

Posted Thursday, July 03, 2008 2:19 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:52 AM
Points: 582, Visits: 1,603

You're right...my comments are quite severe, and probably over the top. So I apologise if I've offended.

But I do think that in an article the presentation is as important as the content, in the sense that the content will never be considered by a large section of the readership, when it is poorly presented.

I also know from experience that writing articles is hard work and takes time. It is however a very rewarding experience, and I'd encourage others to try their hand. However to maximise their chances of their articles being well received, I'd recommend spending a certain time on the "packaging", which can show their technical contributions in the best light.

(But I do think the editor should be a little more proactive in this regard.)

A final point, it is clear that the author has good English, grammar and spelling, and can put together a sentence or paragraph, apparently without too much trouble. I just wish he'd done a couple more, to put his script in context.

Post #527837