Number of Rows

  • Dave62

    SSCertifiable

    Points: 6419

    Comments posted to this topic are about the item Number of Rows

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

    :blink:

    --
    Dineshbabu
    Desire to learn new things..

  • Ravi SQL

    SSCommitted

    Points: 1714

    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: 258941

    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

    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

    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: 10589

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

    Is there a relevance to this?

  • demonfox

    SSCertifiable

    Points: 6289

    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: 64645

    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: 11231

    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

    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]

  • This was removed by the editor as SPAM

  • ralm

    Hall of Fame

    Points: 3117

    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

    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

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

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