Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Number of Rows Expand / Collapse
Author
Message
Posted Friday, January 18, 2013 12:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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


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

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1409054
Posted Friday, January 18, 2013 12:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 2,150, Visits: 2,152
bitbucket-25253 (1/18/2013)
[quote]...
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!
Post #1409071
Posted Friday, January 18, 2013 1:40 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:25 AM
Points: 1,386, Visits: 404
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.
Post #1409088
Posted Friday, January 18, 2013 2:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
Dave62 (1/18/2013)
bitbucket-25253 (1/18/2013)
[quote]...
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...


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

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1409102
Posted Friday, January 18, 2013 3:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 2,150, Visits: 2,152
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!
Post #1409118
Posted Friday, January 18, 2013 7:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1409154
Posted Saturday, January 19, 2013 10:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 2,150, Visits: 2,152
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!
Post #1409214
Posted Monday, January 21, 2013 12:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:37 AM
Points: 305, Visits: 180
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?

Post #1409382
Posted Tuesday, January 22, 2013 4:12 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Thanks for the great question.
Post #1409915
Posted Tuesday, January 22, 2013 7:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:45 PM
Points: 212, Visits: 233
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..
Post #1410045
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse