January 18, 2013 at 12:11 pm
Dave62 (1/18/2013)
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!
Been there done that ... the message tab is NOT, let me repeat, the message tab in NOT part of the estimated execution plan
January 18, 2013 at 12:54 pm
bitbucket-25253 (1/18/2013)
...
Been there done that ... the message tab is NOT, let me repeat, the message tab in NOT part of the estimated execution plan
I think the message tab is very much a part of the estimated execution plan. When you run the exact same commands 3 different ways, the message tab displays differently for each way. This would indicate that what is read in the message tab can vary depending on how the commands are run.
Take these 3 commands run against the AdventureWorks database for example.
Select COUNT(*) From Person.Contact;
Select COUNT(*) From HumanResources.Employee;
Select COUNT(*) From Production.Product;
1) If you just execute the code, you will see 3 messages that say 1 row(s) affected
2) If you only click the Display Estimated Execution Plan button, you will see only 1 message that says 1 row(s) affected
3) If you click the Include Actual Execution Plane button and Execute, you will see 6 messages that say 1 row(s) affected
Enjoy!
January 18, 2013 at 1:40 pm
FYI - running the posted statents using 10.50.4000 I got a response with only the message tab back with the message "Command(s) completed successfully"and 0 rows listed in the bottom right corner when connected against the MASTER database from SSMS on my desktop.
January 18, 2013 at 2:05 pm
Dave62 (1/18/2013)
bitbucket-25253 (1/18/2013)
...
Been there done that ... the message tab is NOT, let me repeat, the message tab in NOT part of the estimated execution plan
I think the message tab is very much a part of the estimated execution plan. When you run the exact same commands 3 different ways, the message tab displays differently for each way. This would indicate that what is read in the message tab can vary depending on how the commands are run.
Take these 3 commands run against the AdventureWorks database for example.
Select COUNT(*) From Person.Contact;Select COUNT(*) From HumanResources.Employee;
Select COUNT(*) From Production.Product;
1) If you just execute the code, you will see 3 messages that say 1 row(s) affected
2) If you only click the Display Estimated Execution Plan button, you will see only 1 message that says 1 row(s) affected
3) If you click the Include Actual Execution Plane button and Execute, you will see 6 messages that say 1 row(s) affected
Enjoy!
I guess the best we can do is agree to disagree and leave it at that...
January 18, 2013 at 3:06 pm
bitbucket-25253 (1/18/2013)
...I guess the best we can do is agree to disagree and leave it at that...
I don't even agree with this statement because I gave a clear and repeatable example of how the message tab can very depending upon how the code is run.
You have not given any clear or repeatable code to support your position. I think opening your mind to the possibility that you are wrong is better than agreeing to disagree in this instance.
Learning can not occur without an open mind.
Enjoy!
January 18, 2013 at 7:31 pm
Dave62 (1/18/2013)
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!
Oh, I see, you didn't mean "how many rows are affected by the following code". Unfortunately that's what you asked. Neither were you asking about rows in an estimated execution plan - because of course you have no code in the question that attempts to select from an estimated execution plan - although that's what the question said it was asking about. Oh well, I suppose that if you are sufficiently careless to express your question that badly I should assume that the response quoted above was not a deliberate attempt to take the piss, to insult, to offend. Now that I know what your question was supposed to mean, I can provide the answer you think is correct. But if you want to know how many rows that code affects, which is what you asked, you have to run the code as well as asking for an estimated execution plan, and that will definitely NOT give you "1 rows affected", and it's reasonable for people to assume that what you asked was what you meant to ask - - and perhaps also to expect you to at least try not to appear to be deliberately offensive. (Your last response to bitbucket appears that way to me too.)
Tom
January 19, 2013 at 10:10 am
L' Eomot Inversé (1/18/2013)
... But if you want to know how many rows that code affects, which is what you asked, you have to run the code as well as asking for an estimated execution plan, and that will definitely NOT give you "1 rows affected"...
This part of your rant is very confusing. You seem to be saying that I did not mention the execution plan in the question.
Here is the exact text. "How many row(s) are affected by the following code in the estimated execution plan?"
As you can see, the estimated execution plan is clearly stated. Also, the only place you will ever see the text describing rows affected is in the message tab. And, if you follow the steps I provided, you will definitely see the message 1 row(s) affected in the only place it ever appears... the message tab.
Sorry to appear defensive but reason would suggest that one would not need to defend if one was not offended. If people did not misread the question and draw erroneous conclusions or make statements without providing code to support their position as I have then maybe I would not have been offended.
I should just let it go. If people want to just come here to complain rather than to learn then that says more about them than me.
Enjoy!
January 21, 2013 at 12:52 am
Dave62 (1/19/2013)
<snip>Here is the exact text. "How many row(s) are affected by the following code in the estimated execution plan?"
As you can see, the estimated execution plan is clearly stated. Also, the only place you will ever see the text describing rows affected is in the message tab. And, if you follow the steps I provided, you will definitely see the message 1 row(s) affected in the only place it ever appears... the message tab.
<snip>
Enjoy!
And this is where you lost me.
You ask for the number of rows that are affected in the estimated exeution plan. The answer is 0.
You then proceed to tell about the one row that the estimated execution plan results in. But that is outside the scope of the estimated execution plan!
So I would concur with whomever has had the sense to ask about a reworded question, such as:
How many rows are affected when you display an estimated execution plan with only one batch?
As somebody told us, then this code:
--
go
--
will result in:
(1 row(s) affected)
(1 row(s) affected)
which would tell us more about rows affected by showing estimated execution plans.
Granted, having the question about number of queries in an execution plan in mind, I were also looking through the execution plan data and finding no rows affected. And the question clearly stated "in the" execution plan, so no reason to look above the scope of the execution plan.
With this in mind, let's all agree to disagree, and continue to all be friends, shall we?
January 22, 2013 at 4:12 am
Thanks for the great question.
January 22, 2013 at 7:55 am
I am a starter in sql server..
Can some one let me know why i dont get the 1 row(s) affected message as stated but simply
Command(s) completed successfully message..
___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:
January 22, 2013 at 8:07 am
venkat9.sql (1/22/2013)
I am a starter in sql server..Can some one let me know why i dont get the 1 row(s) affected message as stated but simply
Command(s) completed successfully message..
Hello, and welcome to SQL Server Central.
We don't actually execute the code to see the 1 row(s) affected message. There is a button near the top of SQL Server Management Studio that will "Display Estimated Execution Plan". If you click this button two tabs will open up in the results pane, an "Execution plan" tab and a "Messages" tab. If you click on the Messages tab, you will see the 1 row(s) affected message there.
Good luck!
January 22, 2013 at 9:18 am
Thanks for the reply. I didn't execute the code. I just used the display estimated execution plan.
I checked the messages tab and didnt get the 1 row affected message.
I am using 2008R2. I fiddled around ssms to see if I can display the rows affected message. but couldn't find anything. I did see few posts preiously stating that they didnt see the message either. I dont know if its a version difference or if its due to any settings in my ssms.
Thanks,
VJ
___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:
January 22, 2013 at 9:32 am
venkat9.sql (1/22/2013)
Thanks for the reply. I didn't execute the code. I just used the display estimated execution plan.I checked the messages tab and didnt get the 1 row affected message.
I am using 2008R2. I fiddled around ssms to see if I can display the rows affected message. but couldn't find anything. I did see few posts preiously stating that they didnt see the message either. I dont know if its a version difference or if its due to any settings in my ssms.
Thanks,
VJ
Menu-option Query / Query Options ... / Advanced / untick SET NOCOUNT (for current query window only)
Or: menu-option Tools / Options / Query Execution / SQL Server / Advanced / untick SET NOCOUNT (default setting for all new query windows; will not affect windows already open)
Or: Execute "SET NOCOUNT OFF;" (for current query window only; can be used to change setting for parts of a longer batch, but will only work when actually executing the code, not when requesting estimated execution plan)
January 22, 2013 at 9:34 am
venkat9.sql (1/22/2013)
Thanks for the reply. I didn't execute the code. I just used the display estimated execution plan.I checked the messages tab and didnt get the 1 row affected message.
I am using 2008R2. I fiddled around ssms to see if I can display the rows affected message. but couldn't find anything. I did see few posts preiously stating that they didnt see the message either. I dont know if its a version difference or if its due to any settings in my ssms.
Thanks,
VJ
Thanks for the feedback. Since submitting the question, I have done further testing on a default installation of 2008 R2 SP1 and have been able to duplicate the results. The other poster who had a similar problem mentioned 2008 R2 SP2 so if you are using SP2 also then I think your conclusion must be correct that it's either a version difference or settings issue.
This makes the behavior even more curious to me because I would think the Messages tab involves basic functionality. I would further expect more documentation if any significant changes occurred between versions. Oh well, I guess there's always something new to learn in SQL Server.
Enjoy!
January 22, 2013 at 10:24 am
Dave62 (1/22/2013)
venkat9.sql (1/22/2013)
Thanks for the reply. I didn't execute the code. I just used the display estimated execution plan.I checked the messages tab and didnt get the 1 row affected message.
I am using 2008R2. I fiddled around ssms to see if I can display the rows affected message. but couldn't find anything. I did see few posts preiously stating that they didnt see the message either. I dont know if its a version difference or if its due to any settings in my ssms.
Thanks,
VJ
Thanks for the feedback. Since submitting the question, I have done further testing on a default installation of 2008 R2 SP1 and have been able to duplicate the results. The other poster who had a similar problem mentioned 2008 R2 SP2 so if you are using SP2 also then I think your conclusion must be correct that it's either a version difference or settings issue.
This makes the behavior even more curious to me because I would think the Messages tab involves basic functionality. I would further expect more documentation if any significant changes occurred between versions. Oh well, I guess there's always something new to learn in SQL Server.
Enjoy!
I already wrote last Friday that showing or suppressing the "xx rows affected" message is an option you can change. See my first message in this topic. In the message just above yours, I explain exactly how to change it.
Nothing curious.
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply