SHOWPLAN_XML

  • Hugo Kornelis (6/13/2013)


    Boy, how do I hate questions like this. No wait, the question is okay, it's the answer options.

    well said.

    Given my utter lack of mindreading skills, I flipped a coin. And I got lucky this time, so I picked the correct answer. Which is a good thing - I expect to see a lot of responses from people demanding their point, so now I have one to give away to them - last time I tried, the local supermarket didn't accept them as payment, so I don't really need them anyway.

    i flipped that same coin and didn't do so well.

  • crussell-931424 (6/13/2013)


    By the way, I added a literal just before the * and got that literal back, so it does execute some data, even if it doesn't retrieve data from the table (which wasn't the specified question anyway).

    How exactly did you do that? Can you post a copy of the modified code?

    I could not (and should not!!) reproduce this in my environment.


    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/

  • +1

    Simone
  • I also think the answers could have been worded different, since it does return a row, but not from the table. Mind reading isn't one of my skills.

  • crussell-931424 (6/13/2013)


    Hugo Kornelis (6/13/2013)


    crussell-931424 (6/13/2013)


    By the way, I added a literal just before the * and got that literal back, so it does execute some data, even if it doesn't retrieve data from the table (which wasn't the specified question anyway).

    How exactly did you do that? Can you post a copy of the modified code?

    I could not (and should not!!) reproduce this in my environment.

    I changed

    Select *

    into

    Select 'CLR1', *

    The CLR1 became part of the text returned in the one row returned.

    You mean, as part of the XML data that is returned there?

    Yes, that makes sense. The entire text of the query is included in the SET SHOWPLAN XML output. You'll find "select" and the table name (address_oltp) in that value as well.


    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/

  • The problem is the missing answer #4: All of the above.

    Because:

    1. the query returns "0 rows" of data

    2. because ShowPlan is on the Select statement does not execute

    3. SQL Server does return "one row" of XML, the query plan details

  • Hugo Kornelis (6/13/2013)


    Given my utter lack of mindreading skills, I flipped a coin. And I got lucky this time, so I picked the correct answer. Which is a good thing - I expect to see a lot of responses from people demanding their point, so now I have one to give away to them - last time I tried, the local supermarket didn't accept them as payment, so I don't really need them anyway.

    And I flipped a coin and got it wrong.

    I was staring at it... and it's back to the land of trying to read the mind of the question writer.

    does the writer consider the one row of xml plan that is returned a row or not? Blah



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • (1 row(s) affected)

    i.e. one row was returned. OK, so it was a row containing XML, not data from the query, but it's still a row 😉

  • mtassin (6/13/2013)


    does the writer consider the one row of xml plan that is returned a row or not?

    but if they do, then there are two correct answers (the sql is not executed, and 1 row is returned).

    Since it's a radio button question, not a check box question, it's clear that they don't consider this row.

  • Thanks to Hugo for his as usual thorough analysis. I faced the same decision but I chose the wrong one.

    Well, I learned something.

  • Revenant (6/13/2013)


    Thanks to Hugo for his as usual thorough analysis. I faced the same decision but I chose the wrong one.

    + 1



    Everything is awesome!

  • Hugo Kornelis (6/13/2013)


    Boy, how do I hate questions like this. No wait, the question is okay, it's the answer options.

    If the question is "how many rows", the answer options should be numbers. All of them. Otherwise, you run the risk of having multiple correct options.

    As happens here. Because of the showplan option, the statement will be compiled and not executed. That is one of the options (though technically not an answer to the question).

    The result of that compilation will then be returned in XML form. That XML is embedded in a result set that has a single column and a single row. So the correct answer is also one. Sheesh!

    Given my utter lack of mindreading skills, I flipped a coin. And I got lucky this time, so I picked the correct answer. Which is a good thing - I expect to see a lot of responses from people demanding their point, so now I have one to give away to them - last time I tried, the local supermarket didn't accept them as payment, so I don't really need them anyway.

    This (badly formulated answers making multiple options correct) has happened too often recently. (No I have no statistics, this comment is based on gut feeling). Question authors, I really appreciate all you do - but before submitting, please take a minute to review the question, and checkk to make sure there is no confusion possible. (And while you're at it, how about copy/popaste the code to a server with a case sensitive collation and a locale setting other then the one you normally use to check if it runs there as well - not an issue with today's question, but I'm in a rant anyway so I'll just spill all).

    Okay, rant is out. Malls, thanks for your question. Despite its flaws, it is an informative question and I'm sure there will be people learning from it.

    I fell into the same boat with the mindreading of the OPs intent.

    I flipped a coin and it came up 1 row.

    I don't really need/want the point. I'd just like to ensure the question and answers are well suited together.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Got it wrong but I did learn something so my attempt wasn't all in vain. 🙂

  • Hugo Kornelis (6/13/2013)


    Boy, how do I hate questions like this. No wait, the question is okay, it's the answer options.

    If the question is "how many rows", the answer options should be numbers. All of them. Otherwise, you run the risk of having multiple correct options.

    As happens here. Because of the showplan option, the statement will be compiled and not executed. That is one of the options (though technically not an answer to the question).

    The result of that compilation will then be returned in XML form. That XML is embedded in a result set that has a single column and a single row. So the correct answer is also one. Sheesh!

    Given my utter lack of mindreading skills, I flipped a coin. And I got lucky this time, so I picked the correct answer. Which is a good thing - I expect to see a lot of responses from people demanding their point, so now I have one to give away to them - last time I tried, the local supermarket didn't accept them as payment, so I don't really need them anyway.

    As the answer you picked isn't technically an answer to the question, I didn't pick it. I picked 1, which is an answer to the question and is how many rows are returned. Of course if it said "how many rows are returned by executing the select statement" the answer would be 0, the answer neither of us picked. I guess I should go for random choices when I see things like this question, since picking the answer nearest to correct almost never wins.

    This (badly formulated answers making multiple options correct) has happened too often recently. (No I have no statistics, this comment is based on gut feeling). Question authors, I really appreciate all you do - but before submitting, please take a minute to review the question, and checkk to make sure there is no confusion possible.

    I share that gut feeling. It would also be good for people, while reviewing as you suggest, to check that the question and answer are not just plain wrong, as that seems to happen too often as well. At least this question doesn't suffer from that.

    Tom

  • Lon-860191 (6/13/2013)


    The problem is the missing answer #4: All of the above.

    Because:

    1. the query returns "0 rows" of data

    2. because ShowPlan is on the Select statement does not execute

    3. SQL Server does return "one row" of XML, the query plan details

    I would have chosen that option if it had been there. And probably found that it was "wrong".

    Tom

Viewing 15 posts - 16 through 30 (of 34 total)

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