SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Wayne Sheffield / SELECT * usage / Latest Posts

RE: SELECT * usage

Dineshbabu — Wed, 09 Jan 2013 05:15:23 GMT

[quote][b]Hugo Kornelis (5/10/2012)[/b][hr][quote][b]jalvarocrespo (5/10/2012)[/b][hr]Tricky question indeed.Well, but I can't find any difference between the result obtained and my response: [b]When the table contains sparse columns and a sparse column set, then any null sparse columns will not be returned.[/b]Isn't it right?[/quote]Your answer says that "any [i]null[/i] sparse columns" (emphasis mine) won't be returned.The correct answer says that "any sparse columns" (null or non-null) won't be returned.[/quote]Great.

RE: SELECT * usage

Iulian -207023 — Mon, 21 May 2012 03:24:33 GMT

that was nice, thank youas per msdn : [url=http://msdn.microsoft.com/en-us/library/cc280521(v=sql.100).aspx][i] CautionAdding 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.[/i][/url]Iulian

RE: SELECT * usage

Britt Cluff — Thu, 17 May 2012 20:09:40 GMT

Good question. Thanks for submitting.

RE: SELECT * usage

Hardy21 — Sun, 13 May 2012 23:29:58 GMT

Great question. Learn something new today...

RE: SELECT * usage

L' Eomot Inversé — Fri, 11 May 2012 12:59:47 GMT

[quote][b]WayneS (5/10/2012)[/b][hr]So phase 2 of my learning is: how could I have done it better?[/quote]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 [quote]not return a column as an individual column in the result set[/quote] 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).

RE: SELECT * usage

IgorMi — Fri, 11 May 2012 11:02:06 GMT

Extra question!I missed it, but I appreciate it much. Learned something new.ThanksIgorMi

RE: SELECT * usage

Koen Verbeeck — Thu, 10 May 2012 12:01:07 GMT

[quote][b]sknox (5/10/2012)[/b][hr]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 [b]very[/b] 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.[/quote]I use the technique in the Microsoft certification exams and it works pretty well :-D

RE: SELECT * usage

Revenant — Thu, 10 May 2012 11:55:03 GMT

[quote][b]Stewart "Arturius" Campbell (5/10/2012)[/b][hr]Interesting question, WayneLearned something new here.[/quote]+1 - foprced me to search MSDN.

RE: SELECT * usage

SQLRNNR — Thu, 10 May 2012 09:59:43 GMT

Good question.

RE: SELECT * usage

Paul White — Thu, 10 May 2012 09:25:06 GMT

[quote][b]WayneS (5/10/2012)[/b][hr]I tried (and sent this QotD to several folks to get their opinions) to make this a good QotD without this controversy.[/quote]Oops! I did see your mail, Wayne, but I was away from home at the time, and it slipped through the net. Sorry :pinch:

RE: SELECT * usage

Jan Van der Eecken — Thu, 10 May 2012 09:22:12 GMT

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.

RE: SELECT * usage

KWymore — Thu, 10 May 2012 09:20:01 GMT

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.

RE: SELECT * usage

mtassin — Thu, 10 May 2012 09:02:15 GMT

[quote][b]Hugo Kornelis (5/10/2012)[/b][hr]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.[/quote]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.

RE: SELECT * usage

Hugo Kornelis — Thu, 10 May 2012 08:53:22 GMT

[quote][b]WayneS (5/10/2012)[/b][hr]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.[/quote]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.

RE: SELECT * usage

WayneS — Thu, 10 May 2012 08:41:48 GMT

[quote][b]Hugo Kornelis (5/10/2012)[/b][hr][quote][b]L' Eomot Inversé (5/10/2012)[/b][hr]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.[quote][b]Hugo Kornelis (5/10/2012)[/b][hr][quote][b]jalvarocrespo (5/10/2012)[/b][hr]Tricky question indeed.Well, but I can't find any difference between the result obtained and my response: [b]When the table contains sparse columns and a sparse column set, then any null sparse columns will not be returned.[/b]Isn't it right?[/quote]Your answer says that "any [i]null[/i] sparse columns" (emphasis mine) won't be returned.The correct answer says that "any sparse columns" (null or non-null) won't be returned.[/quote]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.[/quote]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 [b][i]as an individual column[/i][/b] 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.[/quote]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.

RE: SELECT * usage

EL Jerry — Thu, 10 May 2012 08:38:10 GMT

Interesting question, learned something new today, but it is weird I got the right answer from this page (Sparse Columns Support for OLEDB):[url]http://msdn.microsoft.com/en-us/library/cc280446(v=sql.100).aspx[/url]I agree the wording could've been better, both in the question and on the answers."El" Jerry.

RE: SELECT * usage

WayneS — Thu, 10 May 2012 08:36:51 GMT

[quote][b]Koen Verbeeck (5/9/2012)[/b][hr]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 :-DThe CREATE TABLE reference doesn't describe the behaviour asked in the question. The following article does:[url=http://msdn.microsoft.com/en-us/library/cc280521.aspx]Use Column Sets[/url][quote]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.[/quote][/quote]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!

RE: SELECT * usage

sknox — Thu, 10 May 2012 08:34:17 GMT

[quote][b]L' Eomot Inversé (5/10/2012)[/b][hr][quote][b]Hugo Kornelis (5/10/2012)[/b][hr]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 [b][i]as an individual column[/i][/b] 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.[/quote]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).[/quote]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 [b]very[/b] 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.

RE: SELECT * usage

Carla Wilson-484785 — Thu, 10 May 2012 08:09:21 GMT

Thanks for a very good question. I learned 2 new things today!

RE: SELECT * usage

mtassin — Thu, 10 May 2012 07:46:18 GMT

And now of course... after reading the explanation to Tom, I understand my mistake at reading but not trying to fully comprehend the question. :)

RE: SELECT * usage

mtassin — Thu, 10 May 2012 07:44:22 GMT

[quote][b]Koen Verbeeck (5/9/2012)[/b][hr]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. [/quote]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 returnedThe 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.

RE: SELECT * usage

L' Eomot Inversé — Thu, 10 May 2012 05:19:46 GMT

[quote][b]Hugo Kornelis (5/10/2012)[/b][hr]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 [b][i]as an individual column[/i][/b] 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.[/quote]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).

RE: SELECT * usage

jalvarocrespo — Thu, 10 May 2012 04:42:46 GMT

[quote][b]Hugo Kornelis (5/10/2012)[/b][hr][quote][b]L' Eomot Inversé (5/10/2012)[/b][hr]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.[quote][b]Hugo Kornelis (5/10/2012)[/b][hr][quote][b]jalvarocrespo (5/10/2012)[/b][hr]Tricky question indeed.Well, but I can't find any difference between the result obtained and my response: [b]When the table contains sparse columns and a sparse column set, then any null sparse columns will not be returned.[/b]Isn't it right?[/quote]Your answer says that "any [i]null[/i] sparse columns" (emphasis mine) won't be returned.The correct answer says that "any sparse columns" (null or non-null) won't be returned.[/quote]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.[/quote]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 [b][i]as an individual column[/i][/b] 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.[/quote]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.

RE: SELECT * usage

Hugo Kornelis — Thu, 10 May 2012 04:00:42 GMT

[quote][b]L' Eomot Inversé (5/10/2012)[/b][hr]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.[quote][b]Hugo Kornelis (5/10/2012)[/b][hr][quote][b]jalvarocrespo (5/10/2012)[/b][hr]Tricky question indeed.Well, but I can't find any difference between the result obtained and my response: [b]When the table contains sparse columns and a sparse column set, then any null sparse columns will not be returned.[/b]Isn't it right?[/quote]Your answer says that "any [i]null[/i] sparse columns" (emphasis mine) won't be returned.The correct answer says that "any sparse columns" (null or non-null) won't be returned.[/quote]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.[/quote]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 [b][i]as an individual column[/i][/b] 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.

RE: SELECT * usage

L' Eomot Inversé — Thu, 10 May 2012 03:19:58 GMT

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.[quote][b]Hugo Kornelis (5/10/2012)[/b][hr][quote][b]jalvarocrespo (5/10/2012)[/b][hr]Tricky question indeed.Well, but I can't find any difference between the result obtained and my response: [b]When the table contains sparse columns and a sparse column set, then any null sparse columns will not be returned.[/b]Isn't it right?[/quote]Your answer says that "any [i]null[/i] sparse columns" (emphasis mine) won't be returned.The correct answer says that "any sparse columns" (null or non-null) won't be returned.[/quote]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.

RE: SELECT * usage

Stewart "Arturius" Campbell — Thu, 10 May 2012 02:28:33 GMT

Interesting question, WayneLearned something new here.

RE: SELECT * usage

Hugo Kornelis — Thu, 10 May 2012 02:14:05 GMT

[quote][b]jalvarocrespo (5/10/2012)[/b][hr]Tricky question indeed.Well, but I can't find any difference between the result obtained and my response: [b]When the table contains sparse columns and a sparse column set, then any null sparse columns will not be returned.[/b]Isn't it right?[/quote]Your answer says that "any [i]null[/i] sparse columns" (emphasis mine) won't be returned.The correct answer says that "any sparse columns" (null or non-null) won't be returned.

RE: SELECT * usage

jalvarocrespo — Thu, 10 May 2012 02:06:54 GMT

Tricky question indeed.Well, but I can't find any difference between the result obtained and my response: [b]When the table contains sparse columns and a sparse column set, then any null sparse columns will not be returned.[/b]Isn't it right?

RE: SELECT * usage

Hugo Kornelis — Thu, 10 May 2012 01:09:44 GMT

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.

RE: SELECT * usage

Koen Verbeeck — Wed, 09 May 2012 23:49:22 GMT

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 :-DThe CREATE TABLE reference doesn't describe the behaviour asked in the question. The following article does:[url=http://msdn.microsoft.com/en-us/library/cc280521.aspx]Use Column Sets[/url][quote]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.[/quote]

SELECT * usage

WayneS — Wed, 09 May 2012 23:31:45 GMT

Comments posted to this topic are about the item [B]SELECT * usage[/B]