Number of Rows

Question

Number of Rows

Dave62

SSCertifiable

Points: 7459
More actions
January 17, 2013 at 9:16 pm

#268825

Comments posted to this topic are about the item Number of Rows
January 18, 2013 at 3:57 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

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[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 2

Dineshbabu

Hall of Fame

Points: 3220

January 18, 2013 at 12:02 am

#1578565

:blink:

--
Dineshbabu
Desire to learn new things..

Ravi SQL SSCommitted Points: 1714 More actions · Answer 3

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 SSC Guru Points: 259115 More actions · Answer 4

Always hard to answer a question with no documented behaviour without running it 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 5

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 SSCertifiable Points: 7686 More actions · Answer 6

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 SSChampion Points: 10730 More actions · Answer 7

In my naivete I'm thinking 'so what?'.

Is there a relevance to this?

demonfox SSCertifiable Points: 6289 More actions · Answer 8

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 SSC Guru Points: 64790 More actions · Answer 9

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Toreador SSChampion Points: 11368 More actions · Answer 10

paul s-306273 (1/18/2013)
In my naivete I'm thinking 'so what?'.
Is there a relevance to this?

That was my reaction as well. Especially as actually running the code gives different results.

DugyC Hall of Fame Points: 3804 More actions · Answer 11

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.
< snip very interesting explanation>
But again - back to Paul Knibb's reaction: what is the relevance of this?

+ 1

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

ralm Hall of Fame Points: 3156 More actions · Answer 12

Stewart "Arturius" Campbell (1/18/2013)
paul s-306273 (1/18/2013)
In my naivete I'm thinking 'so what?'.
Is there a relevance to this?
+1

+1

I wanted to see the answer and explanation without spending time in analyzing and selected '1' (lucky).

^{[font="Verdana"]Regards,
Rals[/font].}

sjimmo SSChampion Points: 11139 More actions · Answer 13

In my naivete I'm thinking 'so what?'.
Is there a relevance to this?

Exactly.

BTW I got Command(s) completed successfully.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan