SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Carlo Romagnano / "SELECT @local_variable" / Latest Posts

RE: "SELECT @local_variable"

SQLRNNR — Wed, 07 Jul 2010 15:00:27 GMT

Good question

RE: "SELECT @local_variable"

L' Eomot Inversé — Wed, 30 Jun 2010 09:47:45 GMT

[quote][b]Hugo Kornelis (6/25/2010)[/b][hr]Though the explanation sounds enlightening, it is wrong. And it is also wrong to claim that the result of this query will always be "C".[/quote]Yes, both those statements are true, but it's a fun question anyway. And the discussion will probably be lively and interesting. [quote]Though many people seem to prefer to ignore it, it is widely known that this method of string concatenation is NOT SUPPORTED. Microsoft itself writes (I believe in a Knowledge Base article; I've spent some time trying to hunt it down but failed, unfortunately) that this method is not supported and that the results are unpredictable.As far as I am concerned, any of the alternatives mentioned may be returned, depending on version, SP level and build of SQL Server, hardware used, and amount of other activity on the server.Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!![/quote]Microsoft do NOT state that this technique is unsupported. They state that the result is undefined in some circumstances, and document a workaround that supposedly avoids those circumstances. The workaround is: "In order to achieve the expected results from an aggregate concatenation query, apply any Transact-SQL function or expression to the columns in the SELECT list rather than in the ORDER BY clause." So I will continue to use SELECT @LV to compute aggregate concatenations (and to compute reductions with functions other than string concatenation) in production code. Having read several MS articles on this topic, and being thoroughly unconvinced that the workaround suggested by MS is effective in 100% of cases in all releases of SQL Server (and noting anyway that MS misunderstand the interaction of the ANSI spec of ORDER BY with their own specification of SELECT @LV), I apply a rather tighter restriction: I will not use them with an order by clause or a distinct qualifier (since distinct implies a sort), and I will always use a maxdop hint to restrict parallelism, unless the function in question is both commutative and associative (in which case order by would harmless but I still wouldn't use it because it would also be pointless, and maxdop is irrelevant; distinct still has to be avoided unless the base function has further properties that make it safe). For anything non-trivial I will also include in the production system extra code to check that each aggregate concatenation works as required, just in case MS changes something (I've had enough experience of things stopping working because SQL tightened something up somewhere to be careful about this). The reason for this is that I don't have unbounded computational resources, so I won't use a cursor instead, nor use grossly slow XML parsing, and that leaves me only with aggregate concatenation if I want to efficiently evaluate a reduction outside the hopelessly restricted set provided as built in aggregates in SQL. If this technique does stop working that will be in the future (it's worked in every SQL Server release from 7 to now) when I hope that (a) there will be more computational power (including more data moving power) and that (b) SQL will have moved on to provide a better method of doing this.There may be some interesting variations on the SELECT @LV theme in the MERGE statement. BOL [url]http://msdn.microsoft.com/en-gb/library/bb510625(SQL.100).aspx[/url] provides a syntax for the <set_clause> which explicitly includes "@variable = column = expression" but then expressly states "Setting a variable to the same value as a column is not permitted" - what can that mean? Of course "@variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression" is also specified as allowed in the set clause. Is there any MS documentation on using MERGE to do "aggregate concatenations"? (I haven't seen any.) Maybe SELECT @LV will be completely safe in MERGE because there is no ORDER BY clause? I haven't done any experimentation to find out.One of these days SQL will jump out of it's archaic and primitive world view that avoids catering explicitly for a REDUCE operation and provides instead a limited number of "aggregates" which provide reduction for only a very limited set of elementary functions. In the mean time we are stuck with something less than ideal. Having worked in the 80s and early 90s with people who were active in SQL standardisation and seen the amazing slowness of progress I imagine it will be one of the big software companies that defines the improvement, not the standards bodies.

RE: "SELECT @local_variable"

VM-723206 — Tue, 29 Jun 2010 00:49:46 GMT

I'm not sure why DISTINCT was required here..SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY 1 The above select returns a C as well. Am I missing something here?

RE: "SELECT @local_variable"

antony-688446 — Sun, 27 Jun 2010 16:00:32 GMT

[quote][b]Mauve (6/25/2010)[/b][hr][quote][b]UMG Developer (6/25/2010)[/b][hr][quote][b]Hugo Kornelis (6/25/2010)[/b]Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!![/quote]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.)[/quote]Then the only [u]proper[/u] method is to use a cursor, which will guarantee the result set, and concatenate the values returned by the cursor into the desired string.[/quote]No, you can use the FOR XML PATH method, example given on page 2 of discussion!

RE: "SELECT @local_variable"

Ninja's_RGR'us — Fri, 25 Jun 2010 17:45:53 GMT

[quote][b]Hugo Kornelis (6/25/2010)[/b][hr][quote][b]UMG Developer (6/25/2010)[/b][hr][quote][b]Hugo Kornelis[/b]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.[/quote]You missed that Ninja's post, and the Connect item, had a link to the same KB article...[/quote]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![/quote]No apologies necessary... just funny to see it happen ;-):hehe:.

RE: "SELECT @local_variable"

JohnFx — Fri, 25 Jun 2010 12:50:05 GMT

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.

RE: "SELECT @local_variable"

Mauve — Fri, 25 Jun 2010 12:26:55 GMT

[quote][b]UMG Developer (6/25/2010)[/b][hr][quote][b]Hugo Kornelis (6/25/2010)[/b]Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!![/quote]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.)[/quote]Then the only [u]proper[/u] method is to use a cursor, which will guarantee the result set, and concatenate the values returned by the cursor into the desired string.

RE: "SELECT @local_variable"

Hugo Kornelis — Fri, 25 Jun 2010 12:22:51 GMT

[quote][b]UMG Developer (6/25/2010)[/b][hr][quote][b]Hugo Kornelis[/b]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.[/quote]You missed that Ninja's post, and the Connect item, had a link to the same KB article...[/quote]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!

RE: "SELECT @local_variable"

UMG Developer — Fri, 25 Jun 2010 12:18:57 GMT

[quote][b]Hugo Kornelis (6/25/2010)[/b]Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!![/quote]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.)

RE: "SELECT @local_variable"

UMG Developer — Fri, 25 Jun 2010 12:15:12 GMT

[quote][b]Hugo Kornelis[/b]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.[/quote]You missed that Ninja's post, and the Connect item, had a link to the same KB article...

RE: "SELECT @local_variable"

dbuendiab — Fri, 25 Jun 2010 12:12:55 GMT

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.

RE: "SELECT @local_variable"

Hugo Kornelis — Fri, 25 Jun 2010 12:06:43 GMT

[quote][b]Ninja's_RGR'us (6/25/2010)[/b][hr][quote][b]Hugo Kornelis (6/25/2010)[/b][hr][quote][b]kevin.l.williams (6/25/2010)[/b][hr][quote][b]Hugo Kornelis (6/25/2010)[/b] Is 0.1% chance of incorrect results acceptable for your user?[/quote] Not for me. Hugo, is this the article you were looking for?"[i]The correct behavior for an aggregate concatenation query is undefined.[/i]"[url]http://support.microsoft.com/kb/q287515/[/url][/quote][b]EXACTLY![/b]Thanks for locating it, Kevin. That was exactly the article I had in mind.[/quote]I guess someone forgot to read my post :hehe:[/quote]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.

RE: "SELECT @local_variable"

Ninja's_RGR'us — Fri, 25 Jun 2010 11:42:11 GMT

[quote][b]Hugo Kornelis (6/25/2010)[/b][hr][quote][b]kevin.l.williams (6/25/2010)[/b][hr][quote][b]Hugo Kornelis (6/25/2010)[/b] Is 0.1% chance of incorrect results acceptable for your user?[/quote] Not for me. Hugo, is this the article you were looking for?"[i]The correct behavior for an aggregate concatenation query is undefined.[/i]"[url]http://support.microsoft.com/kb/q287515/[/url][/quote][b]EXACTLY![/b]Thanks for locating it, Kevin. That was exactly the article I had in mind.[/quote]I guess someone forgot to read my post :hehe:

RE: "SELECT @local_variable"

Hugo Kornelis — Fri, 25 Jun 2010 10:15:31 GMT

[quote][b]JF1081 (6/25/2010)[/b][hr]Why is the answer 'C'? Could it be possible that the query would return 'A' or 'B'?[/quote]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.

RE: "SELECT @local_variable"

JF1081 — Fri, 25 Jun 2010 10:02:16 GMT

Why is the answer 'C'? Could it be possible that the query would return 'A' or 'B'?

RE: "SELECT @local_variable"

Hugo Kornelis — Fri, 25 Jun 2010 09:23:21 GMT

[quote][b]kevin.l.williams (6/25/2010)[/b][hr][quote][b]Hugo Kornelis (6/25/2010)[/b] Is 0.1% chance of incorrect results acceptable for your user?[/quote] Not for me. Hugo, is this the article you were looking for?"[i]The correct behavior for an aggregate concatenation query is undefined.[/i]"[url]http://support.microsoft.com/kb/q287515/[/url][/quote][b]EXACTLY![/b]Thanks for locating it, Kevin. That was exactly the article I had in mind.

RE: "SELECT @local_variable"

Mauve — Fri, 25 Jun 2010 09:05:21 GMT

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.[b]Example:[/b]The following code will create a semi-colon delimited list of user names where "NAME" contains names in the format of "LastName, FirstName".[code="sql"]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)[/code]

RE: "SELECT @local_variable"

kevin.l.williams — Fri, 25 Jun 2010 08:46:10 GMT

[quote][b]Hugo Kornelis (6/25/2010)[/b] Is 0.1% chance of incorrect results acceptable for your user?[/quote] Not for me. Hugo, is this the article you were looking for?"[i]The correct behavior for an aggregate concatenation query is undefined.[/i]"[url]http://support.microsoft.com/kb/q287515/[/url]

RE: "SELECT @local_variable"

Hugo Kornelis — Fri, 25 Jun 2010 07:54:21 GMT

[quote][b]OCTom (6/25/2010)[/b][hr]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![/quote]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.

RE: "SELECT @local_variable"

SanjayAttray — Fri, 25 Jun 2010 07:41:32 GMT

Huh..........Still a lot to learn. Trying to understand the programming puzzle.Good question, though.

RE: "SELECT @local_variable"

sknox — Fri, 25 Jun 2010 07:37:19 GMT

[quote][b]OCTom (6/25/2010)[/b][hr]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![/quote]You have to understand Microsoft's definition of "by design". It doesn't solely mean that they actively design the software to act in that way. It can also mean (as in this case) that they design the software to work to a specificiation, and since this use is outside the scope of that specification, they don't ever test for consistent results for it.

RE: "SELECT @local_variable"

Daniel Bowlin — Fri, 25 Jun 2010 06:55:44 GMT

Good question. Took a while to puzzle it out. Thanks.

RE: "SELECT @local_variable"

OCTom — Fri, 25 Jun 2010 06:47:39 GMT

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!

RE: "SELECT @local_variable"

Ninja's_RGR'us — Fri, 25 Jun 2010 04:55:15 GMT

[quote][b]Hugo Kornelis (6/25/2010)[/b][hr]Though the explanation sounds enlightening, it is wrong. And it is also wrong to claim that the result of this query will always be "C".Though many people seem to prefer to ignore it, it is widely known that this method of string concatenation is NOT SUPPORTED. Microsoft itself writes (I believe in a Knowledge Base article; I've spent some time trying to hunt it down but failed, unfortunately) that this method is not supported and that the results are unpredictable.As far as I am concerned, any of the alternatives mentioned may be returned, depending on version, SP level and build of SQL Server, hardware used, and amount of other activity on the server.Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!![/quote]Here, here. I instinctively selected a,b,c and low and behold that's what I got on my sql 2008 machine after answering "wrong"Here's a bug I sent on connect with a very similar technique. I'll paste the ms reply right after the link[url=https://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition]https://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition[/url][quote][b]Microsoft somehwere in 2008 [/b][hr]Thanks for your feedback. The behavior you are seeing is by design. Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior. This can change from release to release or even within a particular server version due to changes in the query plan. You cannot rely on this behavior even if there are workarounds. See the below KB article for more details:http://support.microsoft.com/kb/287515The ONLY guaranteed mechanism are the following:1. Use cursor to loop through the rows in specific order and concatenate the values2. Use for xml query with ORDER BY to generate the concatenated values3. Use CLR aggregate (this will not work with ORDER BY clause)--Umachandar, SQL Programmability Team [/quote]Here's the original question I asked on ssc which lead to the connect being filled.[url=http://www.sqlservercentral.com/Forums/Topic607455-145-1.aspx]http://www.sqlservercentral.com/Forums/Topic607455-145-1.aspx[/url]I won't post the workarounds since they are not supposed to work by design.

RE: "SELECT @local_variable"

Hugo Kornelis — Fri, 25 Jun 2010 03:22:00 GMT

Though the explanation sounds enlightening, it is wrong. And it is also wrong to claim that the result of this query will always be "C".Though many people seem to prefer to ignore it, it is widely known that this method of string concatenation is NOT SUPPORTED. Microsoft itself writes (I believe in a Knowledge Base article; I've spent some time trying to hunt it down but failed, unfortunately) that this method is not supported and that the results are unpredictable.As far as I am concerned, any of the alternatives mentioned may be returned, depending on version, SP level and build of SQL Server, hardware used, and amount of other activity on the server.Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!!

RE: "SELECT @local_variable"

ziangij — Fri, 25 Jun 2010 02:36:01 GMT

thanks, understood now...:-) [quote][b]Christian Buettner-167247 (6/25/2010)[/b][hr][quote][b]ziangij (6/25/2010)[/b][hr]since there is only one column, why is this working as well ?[code="sql"] SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY 2[/code]Result : A,A,B,B,C,C[/quote]There are two "columns", the second column is "@comma".SQL Server "optimizes" this trivial ORDER BY by removing it completely. (If you think about it, you are ordering by a static value in this case which is the same for all rows). You can see this in the execution plan (there is no order by in the execution plan)Now try the following instead, and you will see the sorting is included in the plan:ORDER BY @comma + REPLACE(a,a,'')[/quote]

RE: "SELECT @local_variable"

Cadavre — Fri, 25 Jun 2010 02:15:10 GMT

Damn, confused me, went with A,B,C :-P

RE: "SELECT @local_variable"

Christian Buettner-167247 — Fri, 25 Jun 2010 01:49:48 GMT

[quote][b]ziangij (6/25/2010)[/b][hr]since there is only one column, why is this working as well ?[code="sql"] SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY 2[/code]Result : A,A,B,B,C,C[/quote]There are two "columns", the second column is "@comma".SQL Server "optimizes" this trivial ORDER BY by removing it completely. (If you think about it, you are ordering by a static value in this case which is the same for all rows). You can see this in the execution plan (there is no order by in the execution plan)Now try the following instead, and you will see the sorting is included in the plan:ORDER BY @comma + REPLACE(a,a,'')

RE: "SELECT @local_variable"

Christian Buettner-167247 — Fri, 25 Jun 2010 01:29:51 GMT

[quote][b]rajesh.subramanian (6/25/2010)[/b][hr]Good Question. SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY 1Result : C SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY a.aResult : A,A,B,B,C,Cchanging the order by giving different result...experts please explain?[/quote]Well, in the first case (order by a.a) you are sorting the intermediate result set of selecting from #a.In the second case (order by 1) you are sorting the variable itself. While the first case makes perfectly sense, the second one does not (how do you sort a variable?)

RE: "SELECT @local_variable"

ziangij — Fri, 25 Jun 2010 01:08:13 GMT

since there is only one column, why is this working as well ?[code="sql"] SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY 2[/code]Result : A,A,B,B,C,C[quote][b]rajesh.subramanian (6/25/2010)[/b][hr]Good Question. SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY 1Result : C SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY a.aResult : A,A,B,B,C,Cchanging the order by giving different result...experts please explain?[/quote]

RE: "SELECT @local_variable"

rals — Fri, 25 Jun 2010 00:56:32 GMT

Good Question. SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY 1Result : C SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY a.aResult : A,A,B,B,C,Cchanging the order by giving different result...experts please explain?

RE: "SELECT @local_variable"

UMG Developer — Thu, 24 Jun 2010 23:28:18 GMT

Great question, and a very understandable explanation. Thanks!

RE: "SELECT @local_variable"

Danny Ocean — Thu, 24 Jun 2010 22:56:28 GMT

Really a good question with good explanation.

RE: "SELECT @local_variable"

malleswarareddy_m — Thu, 24 Jun 2010 21:42:04 GMT

good question. but i also expected that result is A,B,C.

"SELECT @local_variable"

Carlo Romagnano — Thu, 24 Jun 2010 20:26:53 GMT

Comments posted to this topic are about the item [B]"SELECT @local_variable"[/B]