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