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 Thursday, July 3, 2008 9:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 266, Visits: 2,593
Jeff: Thanks for the reply!
- Jeannine Jennifer (close enough)
Post #528180
Posted Thursday, July 3, 2008 9:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #528184
Posted Thursday, July 3, 2008 9:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,368, Visits: 1,391
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.



Post #528190
Posted Thursday, July 3, 2008 9:48 AM
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
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! ;)


---------------------------
|Ted Pin >>
Post #528191
Posted Thursday, July 3, 2008 10:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 23,396, Visits: 32,235
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!




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)
Post #528211
Posted Thursday, July 3, 2008 11:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2008 12:37 PM
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.
Post #528279
Posted Thursday, July 3, 2008 11:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, March 31, 2011 1:40 AM
Points: 1,938, 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
Post #528287
Posted Thursday, July 3, 2008 3:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 30, 2012 7:58 PM
Points: 10, Visits: 37
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?
Post #528421
Posted Thursday, July 3, 2008 5:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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 holiday and watching the thing churn for hours.

I'll be putting in some nice DELETE TOP statements.


ATB

Charles Kincaid

Post #528437
Posted Monday, July 7, 2008 11:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 5, 2009 10:44 AM
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
Post #529510
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse