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 Thursday, January 17, 2013 9:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:12 AM
Points: 2,180, Visits: 2,173
Comments posted to this topic are about the item Number of Rows
Post #1408702
Posted Thursday, January 17, 2013 9:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:20 AM
Points: 1,372, Visits: 1,567
I knew there was a discussion on similar question recently, still got the answer wrong

Whilst, I was trying to look at the queries executed in estimated execution plan, my eye caught something. What ever operation is performed after "SET QUOTED_IDENTIFIER ON;", it is treated as a single query along with QUOTED_IDENTIFIER.

Would really appreciate if someone can pitch-in and explain.

Thanks!


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1408703
Posted Friday, January 18, 2013 12:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:08 AM
Points: 987, Visits: 567


--
Dineshbabu
Desire to learn new things..
Post #1408729
Posted Friday, January 18, 2013 12:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 4:06 AM
Points: 976, Visits: 550
I selected "0" and got wrong.

I think it is because 1 row in the system table is being affected and that is the reason the messages tab shows "(1 row(s) affected)". This happens when we click on estimated execution plan button by just having a "--" (commented line) in the query window.


Regards,
Ravi.
Post #1408734
Posted Friday, January 18, 2013 12:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:52 PM
Points: 13,636, Visits: 11,509
Always hard to answer a question with no documented behaviour without running it



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1408739
Posted Friday, January 18, 2013 12:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:08 AM
Points: 987, Visits: 567
If we run the profiler before clicking Display Estimated Execution Plan, we can see SHOWPLAN_XML option is set to ON.
I think due to enabling this set option we are getting as "1 row(s) affected".

Letz wait untill some experts to explain in detail..


--
Dineshbabu
Desire to learn new things..
Post #1408742
Posted Friday, January 18, 2013 1:19 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Dineshbabu (1/18/2013)
If we run the profiler before clicking Display Estimated Execution Plan, we can see SHOWPLAN_XML option is set to ON.
I think due to enabling this set option we are getting as "1 row(s) affected".

Well... you were very close

The "affected row" is the Estimated Execution Plan itself!

Try running
SET SHOWPLAN_XML ON
GO
<Whatever batch you want>
GO
SET SHOWPLAN_XML OFF
GO

and you'll get a result set with one column named "Microsoft SQL Server 2005 XML Showplan" and one row containing an execution plan. That row is displayed as "affected", and it doesn't matter what batch you estimate (it only must be a valid batch, otherwise you'll get a syntax error instead of any number of "affected rows").
Post #1408748
Posted Friday, January 18, 2013 1:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:32 AM
Points: 1,417, Visits: 809
In my naivete I'm thinking 'so what?'.

Is there a relevance to this?

Post #1408750
Posted Friday, January 18, 2013 1:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:19 PM
Points: 1,129, Visits: 1,164
Lokesh Vij (1/17/2013)
I knew there was a discussion on similar question recently, still got the answer wrong

Whilst, I was trying to look at the queries executed in estimated execution plan, my eye caught something. What ever operation is performed after "SET QUOTED_IDENTIFIER ON;", it is treated as a single query along with QUOTED_IDENTIFIER.

Would really appreciate if someone can pitch-in and explain.

Thanks!


I remember the discussion ; but, I tested that to confirm it ; then I got counfused as it came no rows effected . So , I got it wrong
well, I forgot the question while testing it - the Display Estimated plan ..
I don't need coffee, I need less work...

Try executing any select query , it gives 1 rows affected.


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1408756
Posted Friday, January 18, 2013 2:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:02 PM
Points: 6,132, Visits: 8,395
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.

The question itself doesn't appear to make sense to me, but maybe that's because I am not a native English speaker?
It mentions "rows affected in the estimated execution plan - but there are no rows in the estimated plan (it's XML).

I figured that the author meant "how many rows are affected by the following code when you request an estimated execution plan". But that is nonsense as well, because an estimated plan is made by compiling, but not running the code. So no rows can be affected by definition.

If you have SSMS set to return number of rows affected (yes, that is a changeable option!), then you will indeed get the message "1 row(s) affected". That is because a resultset of 1 row is returned from the server to SSMS. That result set contains the XML column holding the execution plan. You always get this "1 row(s) selected" message when requesting an estimated execution plan, it has nothing to do with the code you wrote. (Except when there are parse errors -in that case, you never get to the compilation stage- or multiple batches -in that case, you get one such message for each batch-)


But again - back to Paul Knibb's reaction: what is the relevance of this?



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1408768
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse