Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ted Pin » Deleting batches of rows with TOP
Add to briefcase
46 posts, Page 4 of 5
«««12345»»

Deleting batches of rows with TOP Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
JJ B
Posted Thursday, July 03, 2008 9:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:03 PM
Points: 255, Visits: 2,405
Jeff: Thanks for the reply!
- Jeannine Jennifer (close enough)
Post #528180
Jeff Moden
Posted Thursday, July 03, 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: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #528184
Anipaul
Posted Thursday, July 03, 2008 9:48 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 4,787, Visits: 1,335
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
Ted Manasa
Posted Thursday, July 03, 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
Lynn Pettis
Posted Thursday, July 03, 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 @ 12:02 AM
Points: 21,596, Visits: 27,415
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
Tony Stratton
Posted Thursday, July 03, 2008 11:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 07, 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
dunnjoe
Posted Thursday, July 03, 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
BadBoysDriveAudi
Posted Thursday, July 03, 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
Charles Kincaid
Posted Thursday, July 03, 2008 5:17 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825
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
B.A. Cooper
Posted Monday, July 07, 2008 11:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 05, 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
46 posts, Page 4 of 5
«««12345»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.