# "SELECT @local_variable"

• Comments posted to this topic are about the item "SELECT @local_variable"

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

Malleswarareddy
I.T.Analyst
MCITP(70-451)

• Really a good question with good explanation.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

• Great question, and a very understandable explanation. Thanks!

• Good Question.

SELECT @t = @t + @comma + a.a

,@comma = ','

FROM #a a

ORDER BY 1

Result : C

SELECT @t = @t + @comma + a.a

,@comma = ','

FROM #a a

ORDER BY a.a

Result : A,A,B,B,C,C

changing the order by giving different result...experts please explain?

[font="Verdana"]Regards,
Rals
[/font].
• since there is only one column, why is this working as well ?

` SELECT @t = @t + @comma + a.a`

` ,@comma = ','`

` FROM #a a`

` ORDER BY 2`

Result : A,A,B,B,C,C

rajesh.subramanian (6/25/2010)

Good Question.

SELECT @t = @t + @comma + a.a

,@comma = ','

FROM #a a

ORDER BY 1

Result : C

SELECT @t = @t + @comma + a.a

,@comma = ','

FROM #a a

ORDER BY a.a

Result : A,A,B,B,C,C

changing the order by giving different result...experts please explain?

• rajesh.subramanian (6/25/2010)

Good Question.

SELECT @t = @t + @comma + a.a

,@comma = ','

FROM #a a

ORDER BY 1

Result : C

SELECT @t = @t + @comma + a.a

,@comma = ','

FROM #a a

ORDER BY a.a

Result : A,A,B,B,C,C

changing the order by giving different result...experts please explain?

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?)

Best Regards,

Chris Büttner

• ziangij (6/25/2010)

since there is only one column, why is this working as well ?

` SELECT @t = @t + @comma + a.a`

` ,@comma = ','`

` FROM #a a`

` ORDER BY 2`

Result : A,A,B,B,C,C

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,'')

Best Regards,

Chris Büttner

• Damn, confused me, went with A,B,C 😛

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• thanks, understood now...:-)

Christian Buettner-167247 (6/25/2010)

ziangij (6/25/2010)

since there is only one column, why is this working as well ?

` SELECT @t = @t + @comma + a.a`

` ,@comma = ','`

` FROM #a a`

` ORDER BY 2`

Result : A,A,B,B,C,C

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,'')

• 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!!!!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

• Hugo Kornelis (6/25/2010)

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!!!!

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

https://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition

Microsoft somehwere in 2008

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/287515

The ONLY guaranteed mechanism are the following:

1. Use cursor to loop through the rows in specific order and concatenate the values

2. Use for xml query with ORDER BY to generate the concatenated values

3. Use CLR aggregate (this will not work with ORDER BY clause)

--

Umachandar, SQL Programmability Team

Here's the original question I asked on ssc which lead to the connect being filled.

http://www.sqlservercentral.com/Forums/Topic607455-145-1.aspx

I won't post the workarounds since they are not supposed to work by design.

• 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!

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

• 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!

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.

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply