Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

SELECT * usage Expand / Collapse
Author
Message
Posted Thursday, May 10, 2012 7:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
Koen Verbeeck (5/9/2012)
Good question, learned something.
However, the question itself was a bit weird to read (at least for me, a non-native English speaker). I had to read it a few times to finally get it.


I learned something, and I got it right. But the whole....

When the table contains sparse columns and a sparse column set, then all of the sparse columns will not be returned

The columns come back, but in an XML column.... so depending on semantics, either the first or last answer could be construed as correct.

I'm glad I guessed correctl.y, but... I'd have rather not had to guess as to the intention of the author at the end.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1297883
Posted Thursday, May 10, 2012 7:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
And now of course... after reading the explanation to Tom, I understand my mistake at reading but not trying to fully comprehend the question. :)





--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1297884
Posted Thursday, May 10, 2012 8:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 1,556, Visits: 1,849
Thanks for a very good question. I learned 2 new things today!
Post #1297906
Posted Thursday, May 10, 2012 8:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:04 AM
Points: 1,297, Visits: 1,659
L' Eomot Inversé (5/10/2012)
Hugo Kornelis (5/10/2012)
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.

I guess I have to disagree with myself, too. I was looking at the wording of the answers, not the wording of the question, and the wording of the answers has to be understood in the context of the question and its wording. So the correct answer was indeed the right correct answer (although it was certainly not well worded). My excuse (not a good one) is that one can easily forget the question by the time one has read through all those answers and then read the comments as far as the message I was replying to (which of course repeated the two relevant answers, and not the question).


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.
Post #1297923
Posted Thursday, May 10, 2012 8:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:04 PM
Points: 6,582, Visits: 8,861
Koen Verbeeck (5/9/2012)
Good question, learned something.
However, the question itself was a bit weird to read (at least for me, a non-native English speaker). I had to read it a few times to finally get it. And then answer wrong of course

The CREATE TABLE reference doesn't describe the behaviour asked in the question. The following article does:

Use Column Sets

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.


Dang, I sure wish that I had found this reference when I was looking. This is much better than what I was able to find. Thanks Koen!


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1297926
Posted Thursday, May 10, 2012 8:38 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 3,788, Visits: 1,105
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.


"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan
Post #1297928
Posted Thursday, May 10, 2012 8:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:04 PM
Points: 6,582, Visits: 8,861
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1297931
Posted Thursday, May 10, 2012 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1297939
Posted Thursday, May 10, 2012 9:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
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.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1297950
Posted Thursday, May 10, 2012 9:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 3,266, Visits: 1,957
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.
Post #1297966
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse