June 25, 2010 at 7:41 am
Huh..........Still a lot to learn. Trying to understand the programming puzzle.
Good question, though.
SQL DBA.
June 25, 2010 at 7:54 am
OCTom (6/25/2010)
An unsupported technique? I have seen this used and have been encouraged to use this technique by an experienced consulted. I'll be sending him the link to this discussion. It behaves illogically (at least to me). I expected A,B,C to be the result. I haven't run it but find it disturbing that it would be inconsistent between versions, releases, whatever BY DESIGN!
If a consultant would ever try that on me, I'd fire him on the spot, and tell him what he can do with the bill for his hours worked.
Yes, it does work (*). Usually. Maybe even about 99.9% of the times. Is 0.1% chance of incorrect results acceptable for your user?
(*) Except, of course, in those cases where it does not work. Usually. Such as in this question.
June 25, 2010 at 8:46 am
Hugo Kornelis (6/25/2010) Is 0.1% chance of incorrect results acceptable for your user?
Not for me.
Hugo, is this the article you were looking for?
"The correct behavior for an aggregate concatenation query is undefined."
June 25, 2010 at 9:05 am
Hugo is 100% correct! Using this technique is 100% unpredictable. It will not work with table variables, derived tables, etc.
If you need to make a delimited list of values use XML.
Example:
The following code will create a semi-colon delimited list of user names where "NAME" contains names in the format of "LastName, FirstName".
SELECT STUFF(V.DELIMITED_LIST, 1, 2, N'')
FROM
(
SELECT
N'; ' + U.NAME AS "text()"
FROM
USERS U
WHERE U.NAME IS NOT NULL -- exclude any NULL values
AND U.NAME <> N'' -- exclude any zero-length strings
ORDER BY 1
FOR XML PATH(N'')
) AS V (DELIMITED_LIST)
June 25, 2010 at 9:23 am
kevin.l.williams (6/25/2010)
Hugo Kornelis (6/25/2010) Is 0.1% chance of incorrect results acceptable for your user?
Not for me.
Hugo, is this the article you were looking for?
"The correct behavior for an aggregate concatenation query is undefined."
EXACTLY!
Thanks for locating it, Kevin. That was exactly the article I had in mind.
June 25, 2010 at 10:02 am
Why is the answer 'C'? Could it be possible that the query would return 'A' or 'B'?
June 25, 2010 at 10:15 am
JF1081 (6/25/2010)
Why is the answer 'C'? Could it be possible that the query would return 'A' or 'B'?
Possible? I'd say yes, because the results of this kind of query are undefined (see the previous page if this disussion).
Probable? No, not really. For the reasons mentioned in the explanation of the question.
June 25, 2010 at 11:42 am
Hugo Kornelis (6/25/2010)
kevin.l.williams (6/25/2010)
Hugo Kornelis (6/25/2010) Is 0.1% chance of incorrect results acceptable for your user?
Not for me.
Hugo, is this the article you were looking for?
"The correct behavior for an aggregate concatenation query is undefined."
EXACTLY!
Thanks for locating it, Kevin. That was exactly the article I had in mind.
I guess someone forgot to read my post :hehe:
June 25, 2010 at 12:06 pm
Ninja's_RGR'us (6/25/2010)
Hugo Kornelis (6/25/2010)
kevin.l.williams (6/25/2010)
Hugo Kornelis (6/25/2010) Is 0.1% chance of incorrect results acceptable for your user?
Not for me.
Hugo, is this the article you were looking for?
"The correct behavior for an aggregate concatenation query is undefined."
EXACTLY!
Thanks for locating it, Kevin. That was exactly the article I had in mind.
I guess someone forgot to read my post :hehe:
I did read your post, but the Connect item you linked to was not what I meant when I wrote that I knew this to be documented by MS. The MSKB article Kevin found was.
June 25, 2010 at 12:12 pm
Yet I can't see why the assignation concatenates the strings in one case and does not in the other. Somewhere I read the concatenation behavior is not assured, or is not documented. Maybe is so because of this strange effect.
June 25, 2010 at 12:15 pm
Hugo KornelisI did read your post, but the Connect item you linked to was not what I meant when I wrote that I knew this to be documented by MS. The MSKB article Kevin found was.
You missed that Ninja's post, and the Connect item, had a link to the same KB article...
June 25, 2010 at 12:18 pm
Hugo Kornelis (6/25/2010)Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!!
I agree, but I think variable concatenation is very commonly used, so it is important for people to understand that the ORDER BY clause can really cause you grief. (I think this relates to the running total examples that use local variables.) Personally I prefer using FOR XML to concatenate strings, but there are cases where it doesn't work. (Special characters as one example.)
June 25, 2010 at 12:22 pm
UMG Developer (6/25/2010)
Hugo KornelisI did read your post, but the Connect item you linked to was not what I meant when I wrote that I knew this to be documented by MS. The MSKB article Kevin found was.
You missed that Ninja's post, and the Connect item, had a link to the same KB article...
You are right, I missed that. And not jsut once, but twice. Because I went back and reread Ninja's post when he mentioned being overlooked, and STILL missed the reference.
My apologies, Ninja!
June 25, 2010 at 12:26 pm
UMG Developer (6/25/2010)
Hugo Kornelis (6/25/2010)Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!!
I agree, but I think variable concatenation is very commonly used, so it is important for people to understand that the ORDER BY clause can really cause you grief. (I think this relates to the running total examples that use local variables.) Personally I prefer using FOR XML to concatenate strings, but there are cases where it doesn't work. (Special characters as one example.)
Then the only proper method is to use a cursor, which will guarantee the result set, and concatenate the values returned by the cursor into the desired string.
June 25, 2010 at 12:50 pm
Wow. That was an excellent question that illustrates how important those query plans are and the problem of intermixing a declarative and procedural elements into queries.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply