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
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
Jeff: Thanks for the reply!
- Jeannine Jennifer (close enough)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
Jeff Moden (7/3/2008)
Ted Pin (7/3/2008)
Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"


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.


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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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: 6275 Visits: 1407
cjeremy74 (7/3/2008)
Anirban Paul (7/3/2008)
Is these one practically useful? I have a doubt. Any comments on these?


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.


Thanks for the info... May be this will be helpful for me one day.



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
Jeff Moden (7/3/2008)
Jeff Moden (7/3/2008)
Ted Pin (7/3/2008)
Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"


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.


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.


I would! Wink

---------------------------
|Ted Pin >>
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
Jeff Moden (7/3/2008)
Jeff Moden (7/3/2008)
Ted Pin (7/3/2008)
Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"


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.


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.


So would I!

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Tony Stratton
Tony Stratton
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
Set RowCount may not work in 2008...

From http://msdn.microsoft.com/en-us/library/ms188774.aspx


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.

dunnjoe
dunnjoe
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1938 Visits: 208
I 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.

mu_rauer (7/3/2008)
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

BadBoysDriveAudi
BadBoysDriveAudi
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 38
Has 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 8
Incorrect 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?
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 2383
Other 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.

ATBCharles Kincaid
B.A. Cooper
B.A. Cooper
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 51
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 INT
SET @N = 5
select TOP 5 * from table_name
--select TOP @N * FROM table_name
--select TOP (@N) * FROM table_name
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