Post reply

Deleting batches of rows with TOP

  • July 3, 2008 at 9:33 am

    #837943

    Jeff: Thanks for the reply!

    - Jeannine Jennifer (close enough)

  • July 3, 2008 at 9:35 am

    #837946

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 3, 2008 at 9:48 am

    #837952

    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.

  • July 3, 2008 at 9:48 am

    #837953

    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 >>

  • July 3, 2008 at 10:11 am

    #837971

    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!

    😎

  • July 3, 2008 at 11:33 am

    #838032

    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.

  • July 3, 2008 at 11:42 am

    #838040

    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:

  • July 3, 2008 at 3:40 pm

    #838158

    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?

  • July 3, 2008 at 5:17 pm

    #838168

    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

  • July 7, 2008 at 11:41 am

    #839064

    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

  • July 7, 2008 at 11:49 am

    #839067

    Cooper: 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_name

    I got an error when the parentheses were eliminated.

  • July 7, 2008 at 11:58 am

    #839075

    JJB - 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?

  • July 7, 2008 at 12:06 pm

    #839082

    Cooper: 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.

    - JJ

  • July 7, 2008 at 1:26 pm

    #839135

    JJB - 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.aspx

  • July 7, 2008 at 5:00 pm

    #839219

    B.A. Cooper (7/7/2008)

    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

    For SQL Server 2000, a programmable TOP is NOT available unless you do the dynamic SQL thing... you can, however, use rowcount...

    DECLARE @N INT

    SET @N = 5

    SET ROWCOUNT @N

    SELECT * FROM table_name

    SET ROWCOUNT 0

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 31 through 45 (of 46 total)

You must be logged in to reply to this topic. Login to reply