Post reply

Row limits

  • October 2, 2014 at 8:41 pm

    #74502

    Comments posted to this topic are about the item Row limits

  • October 3, 2014 at 12:50 am

    #1749879

    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.

  • October 3, 2014 at 3:11 am

    #1749904

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

  • October 3, 2014 at 5:09 am

    #1749918

    I learned something today, so thank you.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • October 3, 2014 at 5:57 am

    #1749938

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

  • October 3, 2014 at 6:13 am

    #1749943

    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.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • October 3, 2014 at 6:32 am

    #1749953

    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.

  • October 3, 2014 at 7:19 am

    #1749973

    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!

  • October 3, 2014 at 7:43 am

    #1749982

    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

  • October 3, 2014 at 7:43 am

    #1749983

    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)

  • October 3, 2014 at 9:09 am

    #1750005

    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.

  • October 3, 2014 at 9:13 am

    #1750008

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

  • October 3, 2014 at 9:16 am

    #1750012

    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.

  • October 3, 2014 at 12:06 pm

    #1750060

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

    Aigle de Guerre!

  • October 3, 2014 at 3:01 pm

    #1750097

    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