"SELECT @local_variable"

Question

Post reply

"SELECT @local_variable"

Carlo Romagnano

SSC-Insane

Points: 22682
More actions
June 24, 2010 at 8:26 pm

#223191

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

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

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

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 1

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

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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 2

Really a good question with good explanation.

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

UMG Developer SSChampion Points: 13482 More actions · Answer 3

Great question, and a very understandable explanation. Thanks!

ralm Hall of Fame Points: 3156 More actions · Answer 4

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].}

ziangij SSCrazy Eights Points: 8066 More actions · Answer 5

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?

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

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/

ziangij SSCrazy Eights Points: 8066 More actions · Answer 9

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

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/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

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.

OCTom SSChampion Points: 11785 More actions · Answer 12

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!

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 13

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

sknox SSChampion Points: 12388 More actions · Answer 14

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.