Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Number of Rows


Number of Rows

Author
Message
Dave62
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3054 Visits: 2716
Comments posted to this topic are about the item Number of Rows
Lokesh Vij
Lokesh Vij
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1564 Visits: 1599
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


Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1074 Visits: 569
Blink

--
Dineshbabu
Desire to learn new things..
Ravi SQL
Ravi SQL
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 577
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16455 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1074 Visits: 569
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..
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
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").
paul s-306273
paul s-306273
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1923 Visits: 1060
In my naivete I'm thinking 'so what?'.

Is there a relevance to this?
demonfox
demonfox
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 1192
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 w00t
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 Ermm
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 11579
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search