• Hugo Kornelis (5/10/2012)


    L' Eomot Inversé (5/10/2012)


    Good question, but wrong answer given as right.

    The wrong answer made no points difference to me, because I managed to get it utterly wrong even after reading the Use Column Set page. I supose I could try to hide behind language, like Koen, but I've been speaking English all day every day almost all of my life, so in my case it would be a silly sham - it was just plain sloppy carelessness on my part.

    Incidentally, it is a poor explanation too, since it references a page that tells us exactly nothing about how sparse columns are treated instead of the page with the information; and the disappearance of the XML tags surely shouldn't have been allowed to slip through.

    Hugo Kornelis (5/10/2012)


    jalvarocrespo (5/10/2012)


    Tricky question indeed.

    Well, but I can't find any difference between the result obtained and my response: When the table contains sparse columns and a sparse column set, then any null sparse columns will not be returned.

    Isn't it right?

    Your answer says that "any null sparse columns" (emphasis mine) won't be returned.

    The correct answer says that "any sparse columns" (null or non-null) won't be returned.

    I guess that depends on how you interpreted "returned". The non-null sparse columns are returned in the XML returned for the columnset column, so it isn't really true to say they are not returned. That in fact is the whole point of that columnset column, to enable sparse columns to be returned only for rows where they are not null, so it seems somewhat perverse to claim that they aren't returned when they are not null. You can only get away with that interpretation by assertimng that "returned" means ""returned as individual columns in stead of as XML data in the columnset" which isn't a definition offered in any dictionary I've ever seen.

    I have to disagree with you, Tom.

    Sure, the wording could have been better. I'll immediately agree to that.

    But the question clearly asks: "When does a "SELECT *" statement not return a column as an individual column in the result set (...)" (emphasis mine). The answers only mention that specific columns "... will not be returned" without adding the "as individual column" qualification. When looking at the answers by themselves that could indeed be confusing - but in the context of the question, I think it is clear that this is intended. After all, the answers also don't repeat that this only applies to SELECT * queries. In general, it should not be necessary to repeat the entire question in each answer option.

    Hugo:

    I tried (and sent this QotD to several folks to get their opinions) to make this a good QotD without this controversy. How would you have worded this to have been clearer? In between being explicit about what was needed in the question, and the results where only one answer can possibly give the correct result for what is in the question, I don't see how it would be better.

    Edit: typo. And... I do agree that the reference that Koen provided is much better than what I used.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2