May 10, 2012 at 8:38 am
Interesting question, learned something new today, but it is weird I got the right answer from this page (Sparse Columns Support for OLEDB):
http://msdn.microsoft.com/en-us/library/cc280446(v=sql.100).aspx
I agree the wording could've been better, both in the question and on the answers.
"El" Jerry.
May 10, 2012 at 8:41 am
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
May 10, 2012 at 8:53 am
WayneS (5/10/2012)
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 the 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.
Disclaimer: Life is much easier with 20/20 hindsight. I'm not claiming I would have written it this way before readin this discussion.
Question:
If you execute "SELECT * FROM dbo.MyTable;", in which case with the result set not contain a column for each column in the table dbo.MyTable?
Answer options:
1. Never, "SELECT * FROM dbo.MyTable;" will always return a column for each column in dbo.MyTable.
2. SPARSE columns that contain NULL data will not be included in the result set.
3. Columns of the Geometry data type that are used to describe circular arcs and that contain NULL data will not be included in the result set.
4. SPARSE columns that contain NULL data will not be included in the result set if the table also contains an XML COLUMN_SET column.
5. Any SPARSE columns will not be included in the result set if the table also contains an XML COLUMN_SET column.
I'm sure people will find some issue with the above version as well, but at least I think I have dodged the issue that has confused jalvarocrespo, Tom, Mark, and possibly others.
May 10, 2012 at 9:02 am
Hugo Kornelis (5/10/2012)
I'm sure people will find some issue with the above version as well, but at least I think I have dodged the issue that has confused jalvarocrespo, Tom, Mark, and possibly others.
Actually, pointing a gun at my head and making me read the question instead of just kind of speed reading it, would have made the entire thing easy.
As I said in my second post. Once I actually stopped to read the question slowly, my complaint became a moot point.
May 10, 2012 at 9:20 am
I can't remember the last time I had to read and re-read the Qotd so many times. It definitely made more sense after I read the question, read the answers and re-read the question for the 4th time :-). As I had never used sparse columns or column sets, I figured out the answer from reading up on column sets and then re-reading the question and picking up that the word "Individual" was probably the key. To Hugo's point, I think it would have been a little easier had the question and answers been worded differently.
Regardless, I learned something today. Thanks for the question.
May 10, 2012 at 9:22 am
Yet another reason never to do a SELECT *.
Great question, Wayne! And although I found the reference that Koen supplied, I still got the answer wrong. Anyhow, I learned something. That alone is worth it.
And no, I didn't think that the question was badly worded.
May 10, 2012 at 9:25 am
WayneS (5/10/2012)
I tried (and sent this QotD to several folks to get their opinions) to make this a good QotD without this controversy.
Oops! I did see your mail, Wayne, but I was away from home at the time, and it slipped through the net. Sorry :pinch:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 10, 2012 at 9:59 am
Good question.
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
May 10, 2012 at 11:55 am
Stewart "Arturius" Campbell (5/10/2012)
Interesting question, WayneLearned something new here.
+1 - foprced me to search MSDN.
May 10, 2012 at 12:01 pm
sknox (5/10/2012)
Not a good excuse, but a very good reason. I remember reading somewhere that the best way to read multiple-choice questions is to skim the question, read the answers carefully, and then go back and read the question very carefully, with the answers in mind. This reinforces the context of the question, and focuses you on picking out bits that disqualify the wrong answers.I have no idea if that's true, but it seems to work for me.
I use the technique in the Microsoft certification exams and it works pretty well 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 11, 2012 at 11:02 am
Extra question!
I missed it, but I appreciate it much.
Learned something new.
Thanks
IgorMi
Igor Micev,My blog: www.igormicev.com
May 11, 2012 at 12:59 pm
WayneS (5/10/2012)
So phase 2 of my learning is: how could I have done it better?
Wayne, the question itself is clear, it's the wording of the answer options that makes it a bit unclear.
In the question, you say
not return a column as an individual column in the result set
so the meaning of "return" is clearly qualified.
In four of the five options provided for the answer you use "return" unqualified, which risks that readers will forget the qualification; in the other option you don't use "return" at all, and I think this is best. The other answers could easily be changed to avoid using "return" as well:
First option: just the single word "Never" would do fine.
Second option: could read "When the table contains sparse columns for which no selected row contains a non-null value".
Third option: is ok as it stands.
Fourth option: could read "When the table contains sparse columns and a sparse column set and some of the sparse columns have non-null values in none of the rows selected".
Fifth option: could read "Whenever the table contains sparse columns and a sparse column set".
I think this would have been a good deal clearer, because when one looks at the answers and forgets the qualification of return in the question a different one appears to be correct - assuming that "a null sparse column" has the meaning of the phrase I used above about no no-nulls in that column in the selected rows. Without the repeated use of unqualified "return" pushing one towards the ordinary meaning of return there is no reaon to forget the qualification in the question.
Incidentally, I wouldn't worry too much about a couple of us saying it could be clearer; that can be said of many qa QotD and many an article too (inluding mine, for sure).
Tom
May 13, 2012 at 11:29 pm
Great question. Learn something new today...
Thanks
May 17, 2012 at 8:09 pm
Good question. Thanks for submitting.
http://brittcluff.blogspot.com/
May 21, 2012 at 3:24 am
that was nice, thank you
as per msdn :
Adding a column set changes the behavior of SELECT * queries. The query will return the column set as an XML column and not return the individual sparse columns. Schema designers and software developers must be careful not to break existing applications.
Iulian
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply