Row limits

  • Comments posted to this topic are about the item Row limits

  • Thank you for the post, Steve, good one.

    Happy weekend!!!

    (To all Indians out there - Happy Dussehra)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Thank you Steve. It's always nice when my educated guess matches the documentation!

  • Thanks for the question.

    I'm on a roll lately! πŸ˜€

    But I must say that I do not use this feature in my coding.

    ---------------
    Mel. 😎

  • SqlMel (10/3/2014)


    But I must say that I do not use this feature in my coding.

    I haven't either. I'd use TOP myself, but this is another nice tool to have in the toolbox if you ever need it.

  • I haven't known that ROWCOUNT will override TOP, but I kind of guessed.

    When I tried it, I've noticed that ROWCOUNT locks number of results for the session, so if you run TOP <whatever> later in the same session, it still will be 2, so be mindful.

  • Ed Wagner (10/3/2014)


    SqlMel (10/3/2014)


    But I must say that I do not use this feature in my coding.

    I haven't either. I'd use TOP myself, but this is another nice tool to have in the toolbox if you ever need it.

    +1 Thanks for the question Steve



    Everything is awesome!

  • Ed Wagner (10/3/2014)


    SqlMel (10/3/2014)


    But I must say that I do not use this feature in my coding.

    I haven't either. I'd use TOP myself, but this is another nice tool to have in the toolbox if you ever need it.

    Keep in mind that SET ROWCOUNT is deprecated for INSERT/UPDATE/DELETE and will be removed in future versions of SQL Server

    /HΓ₯kan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

  • Heh... the correct answer isn't actually listed. Answer "D" should be "During the mandatory peer review, you get a trip to the woodshed for a porkchop dinner for writing such junk code". πŸ˜€

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

  • Ed Wagner (10/3/2014)


    SqlMel (10/3/2014)


    But I must say that I do not use this feature in my coding.

    I haven't either. I'd use TOP myself, but this is another nice tool to have in the toolbox if you ever need it.

    This used to come in handy before TOP supported variables.

  • Steve, thanks for the easy question to end the week. I prefer TOP for my queries because its effects don't persist, but several of my peers use SET ROWCOUNT.

    Jeff, your comment made me laugh :laugh: (thanks), and I agree with you, but I don't think a pork chop dinner should be considered punishment. πŸ™‚

  • Jeff Moden (10/3/2014)


    Heh... the correct answer isn't actually listed. Answer "D" should be "During the mandatory peer review, you get a trip to the woodshed for a porkchop dinner for writing such junk code". πŸ˜€

    LOL, perhaps, but imagine

    create proc spReallyBusyProc

    @someparam int

    as

    begin

    set no count on

    set rowcount @someparam

    -- do 40 things, because that's how we write procs

    select top (@y_less_than_@someparam)

    somecol

    from sometable

    -- do 10 more things

    end

    It seems like it might be tricky, but it makes sense.

    Set rowcount 4 - we return 4 rows

    select top 8 - we return 8 rows from the query, but only 4 get displayed because of the above.

  • Interesting...I have never used SET ROWCOUNT or seen it used anywhere before. G.I. JOE!!

    Aigle de Guerre!

  • Meow Now (10/3/2014)


    Interesting...I have never used SET ROWCOUNT or seen it used anywhere before. G.I. JOE!!

    That's actually a good thing; please don't get into the habit of using it now.

    It is already deprecated for INSERT, UPDATE, DELETE. I don't know if it ever supported MERGE. And I can only hope that it will soon be deprecated for SELECT.

    You do not want to know how many seasoned professionals did unspeakable things to their data due to setting SET ROWCOUNT and then forgetting to reset it.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 22 total)

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