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 Wednesday, May 9, 2012 11:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
Comments posted to this topic are about the item SELECT * usage

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 #1297593
Posted Wednesday, May 9, 2012 11:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 13,553, Visits: 10,429
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1297602
Posted Thursday, May 10, 2012 1:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 5,967, Visits: 8,221
Good question, Wayne. And thanks Koen for the additional reference.

Also, the result set in the explanation is not really correct. The ColumnSet row will display XML data. I assume that the internet interface of the website somehow has stripped a lot of the XML tags embedded in that result set. I encourage everyone to copy, paste and execute the demo code posted by Wayne.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1297638
Posted Thursday, May 10, 2012 2:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 31, 2014 9:00 PM
Points: 130, Visits: 87
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?
Post #1297678
Posted Thursday, May 10, 2012 2:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 5,967, Visits: 8,221
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1297681
Posted Thursday, May 10, 2012 2:28 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 @ 8:37 AM
Points: 3,905, Visits: 5,079
Interesting question, Wayne
Learned something new here.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1297687
Posted Thursday, May 10, 2012 3:19 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 8,684, Visits: 9,212
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.


Tom
Post #1297703
Posted Thursday, May 10, 2012 4:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 5,967, Visits: 8,221
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 Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1297725
Posted Thursday, May 10, 2012 4:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 31, 2014 9:00 PM
Points: 130, Visits: 87
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'm absolutely sure that Tom and Me we are not alone.

I think that, at least, you must consider the two responses good.
Post #1297752
Posted Thursday, May 10, 2012 5:19 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 8,684, Visits: 9,212
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).


Tom
Post #1297777
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse