May 17, 2011 at 6:14 am
Nice question!
May 17, 2011 at 6:18 am
Damn it.
I answered correctly, but there was 2 same answers and I choose the wrong one
Or is there some difference between those 2 answers?
1. Vinay, Anup, Amit 2. Vinay, , Amit 3. NULL
1. Vinay, Anup, Amit 3. NULL 2. Vinay, , Amit
:angry:
May 17, 2011 at 6:23 am
May 17, 2011 at 6:37 am
May 17, 2011 at 7:00 am
Good Question and nice to know about the bug in this function.
May 17, 2011 at 7:02 am
tabraham 36699 (5/17/2011)
Another ticky tack problem. Sure, I said 2, because the results were the same, just in a different order. It was the last of the two matching result sets. (No order by clause, so order doesn't matter. 🙂 )By the way, what setting in SQL Server Management Studio must I have set that required me to put semicolons at the end of each command before they would run without error?
Ordering does matter because these are 3 select statements, not one with 3 results. The order comes back consistently because there are separate statements in a batch.
May 17, 2011 at 7:07 am
An interesting question and an odd way for NULL to act in the second statement. As others have said, it seems to be contrary to how NULL is otherwise treated.
May 17, 2011 at 7:18 am
Steve Jones - SSC Editor (5/17/2011)
tabraham 36699 (5/17/2011)
Another ticky tack problem. Sure, I said 2, because the results were the same, just in a different order. It was the last of the two matching result sets. (No order by clause, so order doesn't matter. 🙂 )By the way, what setting in SQL Server Management Studio must I have set that required me to put semicolons at the end of each command before they would run without error?
Ordering does matter because these are 3 select statements, not one with 3 results. The order comes back consistently because there are separate statements in a batch.
Steve, the ordering issue is not that the results are in a different order, but that they are *numbered* results.
1. Vinay, Anup, Amit 2. Vinay, , Amit 3. NULL
1. Vinay, Anup, Amit 3. NULL 2. Vinay, , Amit
Note that on the second line, the numbers (which would seem to correspond to row numbers for the result set) are 1-3-2, not 1-2-3.
I think the intent was for the second line to read this way:
1. Vinay, Anup, Amit 2. NULL 3. Vinay, , Amit
But that's not quite what ended up in the options.
-Ki
May 17, 2011 at 7:26 am
Burned by what SHOULD be versus what IS.
Logically, NULL plus anything should be NULL. Period, end of story. NULL means "unknown" and you can't assume that you can replace NULL with a blank string and have it be right. It should be NULL and NULL on 2 and 3. The fact that it is not is either a bug or else needs a clear BOL discussion.
May 17, 2011 at 7:28 am
I'm going to play devil's advocate here (like MS needs any help...). In my copy of online help the STUFF function is quoted as "The Stuff function inserts a string into another string." Note that the word "Concatonate" is not used. I wouldn't be surprised that an official argument could be made that if there is nothing to "stuff" (or insert) then the amount of characters in the third positional parameter are just removed.
But I'm going to try to remember to monitor Tom's connect post to see what happens.
May 17, 2011 at 7:28 am
Kiara (5/17/2011)
Steve, the ordering issue is not that the results are in a different order, but that they are *numbered* results.
1. Vinay, Anup, Amit 2. Vinay, , Amit 3. NULL
1. Vinay, Anup, Amit 3. NULL 2. Vinay, , Amit
Note that on the second line, the numbers (which would seem to correspond to row numbers for the result set) are 1-3-2, not 1-2-3.
I think the intent was for the second line to read this way:
1. Vinay, Anup, Amit 2. NULL 3. Vinay, , Amit
But that's not quite what ended up in the options.
Well, I'll be. I read this a dozen times and didn't notice that. It's fixed. I'll award back points.
May 17, 2011 at 7:40 am
brazumich (5/17/2011)
I'm going to play devil's advocate here (like MS needs any help...). In my copy of online help the STUFF function is quoted as "The Stuff function inserts a string into another string." Note that the word "Concatonate" is not used. I wouldn't be surprised that an official argument could be made that if there is nothing to "stuff" (or insert) then the amount of characters in the third positional parameter are just removed.
But I'm going to try to remember to monitor Tom's connect post to see what happens.
"Unknown" is not nothing. In standard database calculation, NULL touching anything makes it NULL. There is no argument I can think of to support this activity on STUFF. Tom is completely right on this and words it very well on his connect post.
May 17, 2011 at 7:50 am
Semicolons required at end of each statement??
What setting in SQL Server Management Studio must I have set that required me to put semicolons at the end of each command before they would run without error in a query window? (SMS version 10.0.4000.0)
( Apologies for reposting part of an earleir post. However it doesn't seem to be getting a response buried in the earlier post.)
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 17, 2011 at 7:50 am
jeff.mason (5/17/2011)"Unknown" is not nothing. In standard database calculation, NULL touching anything makes it NULL. There is no argument I can think of to support this activity on STUFF. Tom is completely right on this and words it very well on his connect post.
Oh, I thoroughly agree, I believe behavior using NULL should be consistent everywhere. It's why I selected the incorrect answer where NULL was the output for both the second and third statement. However as I type this two thirds of respondents have the correct answer. Either a lot of people agree with my hypothetical response or everyone else is running the commands before answering. I'll leave that issue to the philosophers.
I just thought it would make for an interesting discussion point.
Viewing 15 posts - 16 through 30 (of 64 total)
You must be logged in to reply to this topic. Login to reply