Number of Rows

  • Ron McCullough

    SSC Guru

    Points: 63877

    Missed what is the suposed correct answer. Got confused - I looked at the execution plan ... did NOT consider the message tab as part of the execution plan .

    If one looks at the actual estimated execution plan for a statement such as Select * FROM tablex there is a line entry for "estimated number of rows". For the code in the QOD there is NO such entry.

    So can someone tell me when the message block became part of the estimated execution plan.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Dave62

    SSCertifiable

    Points: 6419

    paul s-306273 (1/18/2013)


    In my naivete I'm thinking 'so what?'.

    Is there a relevance to this?

    No, I don't think there is any "relevance" so to speak. Some questions are just trivia.

    I was interested and curious about why the message (1 row(s) affected) was being returned. It would appear that some have confirmed my suspicion that the estimated execution plan is the row in question.

    Enjoy!

  • Tee Time

    Hall of Fame

    Points: 3663

    Thanks, good question! ๐Ÿ™‚

    My potentially very flawed logic was to choose the item with "OFF" as the outlier and answer 1.

  • mtassin

    SSC-Insane

    Points: 23096

    Koen Verbeeck (1/18/2013)


    Always hard to answer a question with no documented behaviour without running it ๐Ÿ˜‰

    You can still get it wrong like I did...

    SQL 2008R2 RTM - No rows affected

    SQL 2008R2 SP1 - No rows affected

    SQL 2012 RTM - No rows affected

    Don't have a 2008 SP3 instance to test on.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • mtassin

    SSC-Insane

    Points: 23096

    mtassin (1/18/2013)


    Koen Verbeeck (1/18/2013)


    Always hard to answer a question with no documented behaviour without running it ๐Ÿ˜‰

    You can still get it wrong like I did...

    SQL 2008R2 RTM - No rows affected

    SQL 2008R2 SP1 - No rows affected

    SQL 2012 RTM - No rows affected

    Don't have a 2008 SP3 instance to test on.

    Aaah never mind... missed the whole estimated execution plan, which would have been the plan itself... duh.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Mighty

    SSCrazy Eights

    Points: 8452

    Dave62 (1/18/2013)


    paul s-306273 (1/18/2013)


    In my naivete I'm thinking 'so what?'.

    Is there a relevance to this?

    No, I don't think there is any "relevance" so to speak. Some questions are just trivia.

    I was interested and curious about why the message (1 row(s) affected) was being returned. It would appear that some have confirmed my suspicion that the estimated execution plan is the row in question.

    Enjoy!

    But do you think that posting a QOTD is the way to find out why that message appears?!

  • Dave62

    SSCertifiable

    Points: 6419

    Mighty (1/18/2013)


    Dave62 (1/18/2013)... I was interested and curious about why the message (1 row(s) affected) was being returned. It would appear that some have confirmed my suspicion that the estimated execution plan is the row in question.

    Enjoy!

    But do you think that posting a QOTD is the way to find out why that message appears?!

    Sure. I posted the question and verifiable answer because I found the behavior interesting and curious and thought others may also find it interesting and curious. The learning then typically comes from the ensuing discussion.

    Your profile indicates that you have been a member since 2005. So you have been around here long enough to know that many of the experienced and respected members are of the opinion that the majority of the learning in the QotD comes from the discussion not the question itself.

    A question tests knowledge. A discussion promotes learning.

    Enjoy!

  • Mighty

    SSCrazy Eights

    Points: 8452

    Dave62 (1/18/2013)


    Mighty (1/18/2013)


    Dave62 (1/18/2013)... I was interested and curious about why the message (1 row(s) affected) was being returned. It would appear that some have confirmed my suspicion that the estimated execution plan is the row in question.

    Enjoy!

    But do you think that posting a QOTD is the way to find out why that message appears?!

    Sure. I posted the question and verifiable answer because I found the behavior interesting and curious and thought others may also find it interesting and curious. The learning then typically comes from the ensuing discussion.

    Your profile indicates that you have been a member since 2005. So you have been around here long enough to know that many of the experienced and respected members are of the opinion that the majority of the learning in the QotD comes from the discussion not the question itself.

    A question tests knowledge. A discussion promotes learning.

    Enjoy!

    Not sure why it would matter that I am a member since 2005, but I still think that it would have been better to post you question on the forum instead of wrapping it up as a QOTD. Because that is where we are now, and that is where the discussion takes place. It would at least have saved you from writing an incorrect explanation on why the message appears.

  • Lynn Pettis

    SSC Guru

    Points: 442143

    Mighty (1/18/2013)


    Dave62 (1/18/2013)


    Mighty (1/18/2013)


    Dave62 (1/18/2013)... I was interested and curious about why the message (1 row(s) affected) was being returned. It would appear that some have confirmed my suspicion that the estimated execution plan is the row in question.

    Enjoy!

    But do you think that posting a QOTD is the way to find out why that message appears?!

    Sure. I posted the question and verifiable answer because I found the behavior interesting and curious and thought others may also find it interesting and curious. The learning then typically comes from the ensuing discussion.

    Your profile indicates that you have been a member since 2005. So you have been around here long enough to know that many of the experienced and respected members are of the opinion that the majority of the learning in the QotD comes from the discussion not the question itself.

    A question tests knowledge. A discussion promotes learning.

    Enjoy!

    Not sure why it would matter that I am a member since 2005, but I still think that it would have been better to post you question on the forum instead of wrapping it up as a QOTD. Because that is where we are now, and that is where the discussion takes place. It would at least have saved you from writing an incorrect explanation on why the message appears.

    Seems like everyone is a critic. Perhaps you would like to give writing a QotD a try?

  • Dave62

    SSCertifiable

    Points: 6419

    Mighty (1/18/2013)


    ... Not sure why it would matter that I am a member since 2005 ...

    Because I would expect that someone who has been visiting this site longer than me would have read more posts than me that indicate learning in the QotD's come from the ensuing discussion. I've read many such posts in the lesser amount of time that I've been visiting the site. Perhaps you missed them...

    It would at least have saved you from writing an incorrect explanation on why the message appears.

    The behavior is undocumented so I offered 2 possibilities in my explanation. Apparently, according to the findings of others in this discussion, one of my suggested possibilities was correct. Perhaps you missed that...

    Enjoy!

  • SQLRNNR

    SSC Guru

    Points: 281210

    Hugo Kornelis (1/18/2013)


    paul s-306273 (1/18/2013)


    In my naivete I'm thinking 'so what?'.

    Is there a relevance to this?

    That's what I though as well when answering the question.

    I'm thinking the only value is as trivia

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • sestell1

    SSChampion

    Points: 10230

    I generally like questions regarding peculiar behavior.

    It's too bad the wording of the question was a bit off. I too looked within the estimated execution plan for rows affected, and found none.

  • TomThomson

    SSC Guru

    Points: 104772

    What a bizarre question.

    I like paul s-306273's comment about its relevance to life, the universe, or anything and think the anser is (to paul's question) is none. But I have no objection to irrelevant questions, they can be quite appropriate trivia to entertain when posed as QoTD.

    I agree with Hugo's comment: there are no "rows" in a query plan. In fact that thought had tempted me to answer "0". But I took a wild guess that the "rows" were the things labelled in the estimated plan as queries, and we know from last time approximately this same question was asked that there will be 8 such, so I picked that wrong answer instead of the 0 wrong answer.

    When I saw the explanation I thought "someone has screwed up here - the behaviour reported for SSMS / SQL 2008+SP3 is crazy" so I tried actually running it. I don't get anything but "the commands completed successfully" in the message tab when I run this in SSMS / SQL SP2008 R2+SP2. I've tried all sorts of config changes to try to make it give me a row count message, but nothing worked. If I add a select for a single row before the comit statement, I do (of course) get a row count in the message tab - and that says one row is affected; that seems to me to imply that the other lines - at least for that version of SQL Server - affect no rows, since there's no imaginable way that adding that select can prevent a row being affected that would have been affected but for the presence of that select.

    So either I am missing something, or there's been a change in behaviour between 2008+SP3 and 2008R2+SP3.

    Tom

  • Ken Wymore

    SSCoach

    Points: 16387

    An interesting question that caused discussion. I see no issues here. Thanks for posting it.

  • Dave62

    SSCertifiable

    Points: 6419

    L' Eomot Inversรฉ (1/18/2013)


    ...

    So either I am missing something, or there's been a change in behaviour between 2008+SP3 and 2008R2+SP3.

    Try this:

    1) Paste code into SSMS query analyzer

    2) Click "Display Estimated Execution Plan" button

    3) Click on the "Messages" tab after the estimated execution plan is displayed

    4) See the message "1 row(s) affected"

    Enjoy!

Viewing 15 posts - 16 through 30 (of 49 total)

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