January 18, 2013 at 6:08 am
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.
January 18, 2013 at 6:09 am
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!
January 18, 2013 at 6:36 am
Thanks, good question! 🙂
My potentially very flawed logic was to choose the item with "OFF" as the outlier and answer 1.
January 18, 2013 at 6:37 am
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.
January 18, 2013 at 6:38 am
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.
January 18, 2013 at 6:49 am
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?!
January 18, 2013 at 7:10 am
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!
January 18, 2013 at 7:27 am
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.
January 18, 2013 at 8:05 am
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?
January 18, 2013 at 8:23 am
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!
January 18, 2013 at 9:19 am
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
January 18, 2013 at 9:41 am
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.
January 18, 2013 at 10:55 am
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
January 18, 2013 at 11:00 am
An interesting question that caused discussion. I see no issues here. Thanks for posting it.
January 18, 2013 at 11:10 am
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 48 total)
You must be logged in to reply to this topic. Login to reply