Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Deleting batches of rows with TOP Expand / Collapse
Author
Message
Posted Wednesday, July 2, 2008 11:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, July 3, 2008 1:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, July 3, 2008 1:24 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 645, Visits: 1,844
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
Posted Thursday, July 3, 2008 1:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 449, Visits: 1,856
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
Posted Thursday, July 3, 2008 1:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, July 3, 2008 1:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, July 3, 2008 1:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 4:58 AM
Points: 227, Visits: 268
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
Post #527828
Posted Thursday, July 3, 2008 2:19 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 645, Visits: 1,844
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
Posted Thursday, July 3, 2008 2:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:48 AM
Points: 17, Visits: 63
I like this forum!
Post #527838
Posted Thursday, July 3, 2008 3:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,300, Visits: 1,378
Is these one practically useful? I have a doubt. Any comments on these?


Post #527866
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse