InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 23:46:13 GMT20http://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxThis definitely has a practical application.Quite possible this is no longer relevant with Sql 2k5, but I used to do this extensively in a Sql2k environment with immediate transactional replication. I had two problems that large deletes would cause - the blocking one mentioned earlier, and that fact that a large delete could fill up my transaction replication queue and cause more important updates to wait.I'm not working on that system anymore, so I don't know if Sql 2k5's transactional replication is any different. Another way to solve the problem would have been to simply not replicate deletes (do the deletes on primary and subscribers), but that would have required us to write a bunch of special handling in the software we wrote that setup the replication.A final note - I approached this with set rowcount to limit the number of deletes, and actually polled the database every minute to delete a small batch of rows until it was cleaned up. As horrible as that sounds, it kept my transaction logs from being overwhelmed with deletes and falling behind replicating more important things.Fri, 30 Oct 2009 06:39:09 GMTjteeterhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]B.A. Cooper (7/7/2008)[/b][hr]The following code works, but either of the commented lines produce a syntax error in my environment. When the code from the article is cut and pasted it produces the same error. Is there a configuration setting that allows this to work?DECLARE @N INTSET @N = 5select TOP 5 * from table_name--select TOP @N * FROM table_name--select TOP (@N) * FROM table_name[/quote]For SQL Server 2000, a programmable TOP is NOT available unless you do the dynamic SQL thing... you can, however, use rowcount...[code]DECLARE @N INT SET @N = 5 SET ROWCOUNT @N SELECT * FROM table_name SET ROWCOUNT 0[/code]Mon, 07 Jul 2008 17:00:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxJJB - This DB resides at a client site so who knows when it will get upgraded. Thanks again for your input.For others, I confirmed what JJB mentioned in an earlier post. This syntax is only available with a compatability level of 90 which is only available in SQL server 2005. See the following for additional information: http://msdn.microsoft.com/en-us/library/ms178653.aspxMon, 07 Jul 2008 13:26:47 GMTB.A. Cooperhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxCooper: You may be right. It may be a new feature of SS05. You could probably verify that by looking for SS05 new features list somewhere.The biggest hit we got in upgrading to SS05 was the time consuming, hair pulling, ugly SSIS package creation and on-going maintenance. But the new SS05 t-SQL syntax options were a great benefit and productivity enhancer, at least for me. Despite the SSIS sore spot, I'm glad we upgraded, because access to syntax such as the one you are asking about is worth it. Maybe you could get your company to upgrade. Though lots of people are saying that if you haven't upgraded so far, you might as well wait for SS08. That's something you could start lobbying for now.Good luck.- JJMon, 07 Jul 2008 12:06:39 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxJJB - Thanks for your input.The database instance I was trying this with is SQL Server 2000 and the highest compatibility level available is 80. So I have to assume that this syntax will only work with SQL Server 2005 and above?Mon, 07 Jul 2008 11:58:47 GMTB.A. Cooperhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxCooper: My database is set to compatibility of 90. To check this setting, right click on the database in Management Studio and choose Properties. Select the "Options" row from the list on the left. Then look at compatibility level on the right.The 90 setting may or may not make a difference. I got the following syntax to work with the 90 setting:. SELECT TOP (@N) * FROM table_nameI got an error when the parentheses were eliminated.Mon, 07 Jul 2008 11:49:50 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxThe following code works, but either of the commented lines produce a syntax error in my environment. When the code from the article is cut and pasted it produces the same error. Is there a configuration setting that allows this to work?DECLARE @N INTSET @N = 5select TOP 5 * from table_name--select TOP @N * FROM table_name--select TOP (@N) * FROM table_nameMon, 07 Jul 2008 11:41:07 GMTB.A. Cooperhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxOther than a formatting issue (narrow code window) I found this a good reminder about something I'm just about to do.I'm revising a data archive procedure. Somethings don't need to go into the archive, they just need to go away by date. The whole point of the new archive is to to a little each day rather than taking the whole plant down over a weekend, or :w00t: holiday and watching the thing churn for hours.I'll be putting in some nice DELETE TOP statements.Thu, 03 Jul 2008 17:17:56 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxHas anyone actually tried this example? I have and cannot seem to get it to work. Here's the error I get:Server: Msg 156, Level 15, State 1, Line 8Incorrect syntax near the keyword 'top'.So, alas, I'm back to my starting point for paging records. I initially thought I could use the TOP clause in conjunction with a user-defined variable number to return 10 records, 20, 100,...The only difference I see in this example and what I originally came up with was the parenthesis around the variable, so I thought I hit the holy grail with this article. I guess not. Perhaps there's a SQL Server config setting that I need to dig up?Thu, 03 Jul 2008 15:40:32 GMTBadBoysDriveAudihttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxI have always used rowcount (hang over from Sybase days)But using top in the delete would save doing the rowcount. I've forgotten the set rowcount a enough times that using top would help deleting the whole table.I like your solution better than the author's for one more reason, it doesn't use count(*), as noted the tables could be huge, and count(*) would then be very expensive.[quote][b]mu_rauer (7/3/2008)[/b][hr]I am wondering, why not use SET ROWCOUNT ?Normally I am using rowcount because it is really easy to handle.Set Rowcount @nselect 1While @@ROWCOUNT > 0BEGIN Delete from .... where x = yENDShould do the same without too much calculation and too many variables :hehe:[/quote]Thu, 03 Jul 2008 11:42:54 GMTdunnjoehttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxSet RowCount may not work in 2008...From [url=http://msdn.microsoft.com/en-us/library/ms188774.aspx ]http://msdn.microsoft.com/en-us/library/ms188774.aspx[/url][quote]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.[/quote]Thu, 03 Jul 2008 11:33:12 GMTTony Strattonhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]Jeff Moden (7/3/2008)[/b][hr][quote][b]Jeff Moden (7/3/2008)[/b][hr][quote][b]Ted Pin (7/3/2008)[/b][hr]Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"[/quote]Nah... I'd get it wrong... I think I need to find a couple of URLs to point folks to on how to write technical articles.[/quote]Wow! I just did a search on "How to write technical articles".... came up with lot's of hits... looked at the first ten and... they're way too long and complicated for most people to want to read. You might be right... I might have to write a simplified article on a guide of how to write a simple technical article for the forum. Not sure I could get anyone to read even that, though.[/quote]So would I!:cool:Thu, 03 Jul 2008 10:11:04 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]Jeff Moden (7/3/2008)[/b][hr][quote][b]Jeff Moden (7/3/2008)[/b][hr][quote][b]Ted Pin (7/3/2008)[/b][hr]Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"[/quote]Nah... I'd get it wrong... I think I need to find a couple of URLs to point folks to on how to write technical articles.[/quote]Wow! I just did a search on "How to write technical articles".... came up with lot's of hits... looked at the first ten and... they're way too long and complicated for most people to want to read. You might be right... I might have to write a simplified article on a guide of how to write a simple technical article for the forum. Not sure I could get anyone to read even that, though.[/quote]I would! ;)Thu, 03 Jul 2008 09:48:38 GMTTed Manasahttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]cjeremy74 (7/3/2008)[/b][hr][quote][b]Anirban Paul (7/3/2008)[/b][hr]Is these one practically useful? I have a doubt. Any comments on these?[/quote]I actually did use this about a month ago... we had a new policy implemented that allowed us to delete a couple of years worth of data out of several audit tables. The largest delete was approx 2.5 million rows. Agreed probably not an every day use, but does get us less experienced DBA's thinking beyond getting something to just work.[/quote]Thanks for the info... May be this will be helpful for me one day.Thu, 03 Jul 2008 09:48:10 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]Jeff Moden (7/3/2008)[/b][hr][quote][b]Ted Pin (7/3/2008)[/b][hr]Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"[/quote]Nah... I'd get it wrong... I think I need to find a couple of URLs to point folks to on how to write technical articles.[/quote]Wow! I just did a search on "How to write technical articles".... came up with lot's of hits... looked at the first ten and... they're way too long and complicated for most people to want to read. You might be right... I might have to write a simplified article on a guide of how to write a simple technical article for the forum. Not sure I could get anyone to read even that, though.Thu, 03 Jul 2008 09:35:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxJeff: Thanks for the reply!- Jeannine Jennifer (close enough)Thu, 03 Jul 2008 09:33:31 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]JJ B (7/3/2008)[/b][hr]Jeff: You comments about using the wait has me thinking. (Always dangerous.) I understood why one would break up a delete of a lot of rows, but I didn't think one would need a 'wait' to let other statement 'get in'. Here's how I would think it would work: There is a que of requests to get things done and the requests are taken one at a time. The delete statements are adding requests very quickly, but if any other requests come in during that time, they get added to the que and would run in their turn, making other delete requests wait their turn. So, why would a 'wait' statement really help anything? If there is say a 10 second delay between each delete statement and no other requests come in during that time, then it is 10 seconds of wasted time. And if another request had come in during say delete iteration #2, even if delete iteration #3 pops up right away, the other request would run between iteration #2 and #3. I would think.I really don't know how it works. (Please don't anyone take the above statements as a description of the way SQL Server works.) I'm just trying to explain why your recommendation to use a wait command is confusing to me. It doesn't fit with my understanding of how SQL Server works.Thanks,- JJ[/quote]Good points, Jennifer (I got the name right, didn't I?)... you could run sp_locks to see if anyone else had locks on the table to see if you want to wait the delay. But, normally, people make the deletes large enough and the table is in a condition to lock the whole table during the delete. The delay keeps the delete from becoming rather exclusive. Like anything else, "it depends".Thu, 03 Jul 2008 09:29:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxJeff: You comments about using the wait has me thinking. (Always dangerous.) I understood why one would break up a delete of a lot of rows, but I didn't think one would need a 'wait' to let other statement 'get in'. Here's how I would think it would work: There is a que of requests to get things done and the requests are taken one at a time. The delete statements are adding requests very quickly, but if any other requests come in during that time, they get added to the que and would run in their turn, making other delete requests wait their turn. So, why would a 'wait' statement really help anything? If there is say a 10 second delay between each delete statement and no other requests come in during that time, then it is 10 seconds of wasted time. And if another request had come in during say delete iteration #2, even if delete iteration #3 pops up right away, the other request would run between iteration #2 and #3. I would think.I really don't know how it works. (Please don't anyone take the above statements as a description of the way SQL Server works.) I'm just trying to explain why your recommendation to use a wait command is confusing to me. It doesn't fit with my understanding of how SQL Server works.Thanks,- JJThu, 03 Jul 2008 09:14:12 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]Ted Pin (7/3/2008)[/b][hr]Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"[/quote]Nah... I'd get it wrong... I think I need to find a couple of URLs to point folks to on how to write technical articles.Thu, 03 Jul 2008 09:10:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxI meant that (as a relative begginer - certainly in terms of Database Admin if not design) it's actually easier to be introduced to a topic quickly at a high level than have to work through a long article that's packed with detail, where I would probably not have had the time to take all the finer points in.As I'm not a DBA I hadn't even considered the issue of having to delete millions of rows at once. (I'm the person whos buggy code fills the table with rubbish, and haven't had to go thru the pain of taking it all out again... :)) However - today I've not only learned that there's a real performance benefit to deleting in batches, but I've also learned a great number of the finer points of actually doing so because of the discussion all you more knowlegeable guys have had afterwards.In my latest job, we've potentially got a lot of cleansing work to do, so now I can make sure your best practices are brought to the table when we do it.Thu, 03 Jul 2008 09:06:36 GMTdavid.gerrard-604066http://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxThe example was a bit confusing, mostly because "DELETE TOP(2)" threw me off, but this would be very useful in deleting large amount of data on a production system. I deal with this kind of thing regularly, for archiving tracking information. Keeping tables like this clean is essential in having a smoothly running mechanism with lightening fast inserts/updates.What about using something like this? It may be a bit slower using the EXISTS clause, but it would still avoid many of the locking pitfalls of deleting large record sets simultaneously:[code]while exists (select col1 from tab1) delete top(2) from tab1[/code]Thu, 03 Jul 2008 08:57:05 GMTKit Brandnerhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote]The purpose of doing looped Deletes is usually two fold...1. Do the deletes and still allow the table to be used.2. Keep the transaction log small.Item #2 above can only be accomplished if the DB is in the simple recovery mode or there is some additional code in the loop that truncates the log while it is running. [/quote]Going with #2, if the database is in Bulk-Logging or full recovery model, a transaction log backup is what really needs to be there to limit the growth of the t-log. Just wanted to expand on this (even though backup log with truncate only is being depreciated, someone may do that instead of actually backing up the log).:cool:Thu, 03 Jul 2008 08:52:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]Jeff Moden (7/3/2008)[/b][hr][quote][b]Ted Pin (7/3/2008)[/b][hrI looked around for an article that guides article writing, but couldn't find one. Would a prolific contributor like to write for us? :hehe:[/quote]You mean the "Sexy Enough for Dinner Conversation?" site? No, I'll pass... I like to get all my "punishment" through a single source. :) But I very much appreciate the offer.You seem to write a lot on that site, Ted... in fact, quite the writer you are. So, I'm curious... Why [i]did [/i]you shortcut this article on "deletes" so badly?Also, just to be sure... the intent of my comments was not to punish... they were meant to be suggestions. But, just like one of your articles said, "Because IT lacks an inherent human element,..." it is sometimes difficult to make a suggestion or comment on something without coming across that way. Sorry...[/quote]Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"Thanks for looking at my blog. To answer your question, I'm not really sure why I missed the mark so badly. I suppose I've had a lot of practice writing editorial/essay-style articles but remain very ignorant of technical article standards. This I hope to rectify, thanks in no small part to your, and others', well-directed feedback.GermanDBA, my wife thinks I am a stickler for punishment but maybe that's because it works to her advantage =D In all truth, I very much appreciate the comments made herein. I, like many "writers," continually learn to take criticism and try to improve. The response to this article has really been a wake up call for me and that can only be a good thing.Thu, 03 Jul 2008 08:40:36 GMTTed Manasahttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]david.gerrard (7/3/2008)[/b][hr]Hey- I've learned a whole lot from the discussion - more than I would have done if I'd had to skim read a much longer article.[/quote]I'm not sure I understand that... you'll read the discussions, the total of which is a dozen times longer than the article, and interact on some of those discussions... yet, you would only "skim read" a much longer article? Then, you may want to scan the "scripts" section of this forum... most everything there is about the same length as this "article".Just to clarify... my main objection has nothing to do with the length of the article. My objection is because the code doesn't necessarily provide the "nice availability and logging benefits" stated in the first paragraph of the article and additional research before writing the article would have shown that.Thu, 03 Jul 2008 08:37:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]Ted Pin (7/3/2008)[/b][hr]@Jeff:Thanks for your feedback. I will try to create a more well thought-out article for my next submission based on the comments in this discussion. I looked around for an article that guides article writing, but couldn't find one. Would a prolific contributor like to write for us? :hehe:[/quote]You mean the "Sexy Enough for Dinner Conversation?" site? No, I'll pass... I like to get all my "punishment" through a single source. :) But I very much appreciate the offer.You seem to write a lot on that site, Ted... in fact, quite the writer you are. So, I'm curious... Why [i]did [/i]you shortcut this article on "deletes" so badly?Also, just to be sure... the intent of my comments was not to punish... they were meant to be suggestions. But, just like one of your articles said, "Because IT lacks an inherent human element,..." it is sometimes difficult to make a suggestion or comment on something without coming across that way. Sorry...Thu, 03 Jul 2008 08:10:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxHi Ted,please don't take the comments as punishment. I think that all comments/questions with regard to your article were to clarify any points that your target audience couldn't get from just reading the article...... but then again. If you enjoy punishment we can do that too :DThu, 03 Jul 2008 07:46:07 GMTWilliamD-http://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]david.gerrard (7/3/2008)[/b][hr]Hey- I've learned a whole lot from the discussion - more than I would have done if I'd had to skim read a much longer article.[/quote]David, thank you for being the sole clapper in a tough crowd ;) I'm glad this little piece was able to help someone (which was the most I had hoped for). You'll help me get back up on stage for more punishment =DThu, 03 Jul 2008 07:25:29 GMTTed Manasahttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxHey- I've learned a whole lot from the discussion - more than I would have done if I'd had to skim read a much longer article.I'm clearly a much less experienced database person that all you lot (I'm a .Net analyst / developer that seems to have to do more and more database work with every passing day). So short, basic articles that gloss over some of the finer details may not work quite so well for you guys (who already know the basics and more) but they're a big help for me. (I guess DBAs don't really do "glossing over details" though, eh? That's how millions of rows get deleted by accident! ;))I've had a quick look but couldn't see if the articles have a 'level' or 'suitable for...' category attached to them. Maybe that would help?Thu, 03 Jul 2008 07:20:28 GMTdavid.gerrard-604066http://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx@Jeff:Thanks for your feedback. I will try to create a more well thought-out article for my next submission based on the comments in this discussion. I looked around for an article that guides article writing, but couldn't find one. Would a prolific contributor like to write for us? :hehe:Thu, 03 Jul 2008 07:18:33 GMTTed Manasahttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxThanks for all of your responses.This method has been helpful to us for purging log tables and history in our data warehouse. I realize that partitioned tables would have been a better way to handle history purges, but we were not able to implement partitioned tables for various reasons. (Maybe Phase II...)As for why @cnt had to be DECIMAL: Say you had 11 rows to delete, and you set a TOP value of 5. Obviously, you would need 3 iterations of the DELETE to capture the last (1) record. However, when figuring out how many iterations you need, 11/5 = 2.2, which is a DECIMAL (which you use CEILING() on to get the next largest INT). AFAIK, in order to get a decimal result (2.2), the dividend itself must be of type DECIMAL.Thu, 03 Jul 2008 07:09:15 GMTTed Manasahttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]GermanDBA (7/3/2008)[/b][hr]Also, would this not be even better using a numbers table á la Jeff Moden [url]http://www.sqlservercentral.com/articles/TSQL/62867/[/url] ?[/quote]Thank you for the reference... but the Tally table isn't a panacea for all looping problems. The loop here is a process control loop that controls how many times a DELETE is used... I don't believe a Tally table could be used here. If someone can think of a way, please post it because THAT would be truly useful. :) Heck, if you can think of a way, write an article on it! :DThe reasons [b][i]why [/i][/b]someone would want to do a delete like this has been totally missed...The purpose of doing looped Deletes is usually two fold...1. Do the deletes and still allow the table to be used.2. Keep the transaction log small.Item #2 above can only be accomplished if the DB is in the simple recovery mode or there is some additional code in the loop that truncates the log while it is running. The big thing is Item #1... how to do the (massive) deletes and still allow the table to be used by others. For that, you have to have a delay in the loop and you can do that using [b]WAITFOR DELAY '00:00:10' [/b]where the number 10 is some number of seconds. The loop that's listed in the article (and in the thread), whether ROWCOUNT or TOP is used, is fairly agressive and won't necessarily allow other processes to "get in". It's so agressive that you might as well just do the delete all at once. In order for the loop to actually allow other processes to work, the TOP or ROWCOUNT should be small enough to limit any locking to about a second and then the delay should be long enough to allow most other processes to get in and get out... I would think that something between 3 and 10 seconds would be good enough.Shifting gears a bit... I'm really torn... I very much encourage folks to write articles on this forum... the simple sharing of ideas is what the forum is all about... and, I very much encourage folks that read them to remember that not everyone who can write code can also write a masterpiece article. But, I do feel that there were a large number of points missed in this article and, perhaps, some things in the code that violate what some would consider to be a best practice (you don't need to count loops to do this).It was pointed out that [i]"For larger tables, where this technique is most effective, you would obviously use larger values for @N and probably constrain on a date column."[/i] What a prime opportunity for several explanations... [b][i]Why[/i][/b] is this technique most effective for larger tables? [b][i]Why [/i][/b]would you probably constrain ona a date column? [b][i]Why [/i][/b]would you want to use larger values for @N?So, like I said, I'm really torn... I really want people to be encouraged to write without fear of being bashed, but I do have to agree with what some have already said... a bit more thought, a bit more research, and a bit more explanation of the "Why" of "implied points made" would have gone far into turning what should have been a casual script submission into a well written and informative article.Heh... of course, sometimes it's not actually the article that counts... sometimes the discussions that follow are more useful than the original intent of the article. In that case, this article has more than done it's job. ;)Thu, 03 Jul 2008 07:08:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]cjeremy74 (7/3/2008)[/b]I actually did use this about a month ago... we had a new policy implemented that allowed us to delete a couple of years worth of data out of several audit tables. The largest delete was approx 2.5 million rows. Agreed probably not an every day use, but does get us less experienced DBA's thinking beyond getting something to just work.[/quote]Same with me, we do have a logging table where all the errors are stored. If there is a small bug in the system, there can be aprox. 3000 rows per second. So if you fix the bug in a few minutes there are still tons of rows in the table and there is an insert for sure if you want to delete the rows all at once.So it is useful but as I wrote a few rows earlier, I do prefer rowcount but it doesn't matter how you solve a problem as long as you can solve it with as less work for the server as possible.Thu, 03 Jul 2008 06:53:40 GMTChristoph Dhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxOn top of these one off deletions there will undoubtedly be cases when such a delete needs to be done. Off the top of my head, I would say a logging table may well need this type of delete to be done.The original programmer of the TOP option in t-sql had a reason back then, that was a good enough reason to allow this behaviour :PEDIT: I find the TOP option good when doing updates on production tables which can otherwise cause blocking. It's not always nice, but this is real life and not a walt disney film !Thu, 03 Jul 2008 06:42:37 GMTWilliamD-http://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspx[quote][b]Anirban Paul (7/3/2008)[/b][hr]Is these one practically useful? I have a doubt. Any comments on these?[/quote]I actually did use this about a month ago... we had a new policy implemented that allowed us to delete a couple of years worth of data out of several audit tables. The largest delete was approx 2.5 million rows. Agreed probably not an every day use, but does get us less experienced DBA's thinking beyond getting something to just work.Thu, 03 Jul 2008 06:08:03 GMTcjeremy74http://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxI like the following a bit better as you don't have to find out how many rows you're going to affect before affecting them. YMMV.[code]declare @rowcount intset @rowcount = 1declare @batchsize intset @batchsize = 5000while(@rowcount > 0)begin delete top @batchsize from table where foo=1 set @rowcount = @@rowcountend[/code]Thu, 03 Jul 2008 05:48:17 GMTBen Thulhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxI thought the same... "Why would I ever need to delete 5,000,000 rows from a huge database table?" But then I thought "you never know!" It's not the sort of thing you would do every day (I hope) but it's more likely you'd need to do it in response to some disaster or other. And knowing a way to do so effectively without making the situation worse might save your skin one day.Thu, 03 Jul 2008 03:21:05 GMTdavid.gerrard-604066http://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxIs these one practically useful? I have a doubt. Any comments on these?Thu, 03 Jul 2008 03:16:56 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxI like this forum!Thu, 03 Jul 2008 02:21:06 GMTdavid.gerrard-604066http://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxYou'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.Thu, 03 Jul 2008 02:19:05 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic527756-1317-1.aspxI am wondering, why not use SET ROWCOUNT ?Normally I am using rowcount because it is really easy to handle.Set Rowcount @nselect 1While @@ROWCOUNT > 0BEGIN Delete from .... where x = yENDShould do the same without too much calculation and too many variables :hehe:Thu, 03 Jul 2008 01:58:14 GMTChristoph D