Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleting batches of rows with TOP


Deleting batches of rows with TOP

Author
Message
Ted Manasa
Ted Manasa
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 299
Comments posted to this topic are about the item Deleting batches of rows with TOP

---------------------------
|Ted Pin >>
david.gerrard-604066
david.gerrard-604066
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 63
I'm probably missing something here:

Why does that example do anything different to:

DELETE FROM tab1 WHERE col1 = 1;

???
David McKinney
David McKinney
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 2090
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.
w.durkin@online.de
w.durkin@online.de
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 1879
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
david.gerrard-604066
david.gerrard-604066
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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?
david.gerrard-604066
david.gerrard-604066
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 63
I see. (I thought there would probably be a reason in there somewhere). Thanks.
Christoph D
Christoph D
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 348
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
David McKinney
David McKinney
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 2090
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.
david.gerrard-604066
david.gerrard-604066
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 63
I like this forum!
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6279 Visits: 1407
Is these one practically useful? I have a doubt. Any comments on these?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search