## "SELECT @local_variable"

 Author Message Carlo Romagnano SSChampion Group: General Forum Members Points: 12418 Visits: 3517 Comments posted to this topic are about the item "SELECT @local_variable" malleswarareddy_m Hall of Fame Group: General Forum Members Points: 3857 Visits: 1189 good question. but i also expected that result is A,B,C. MalleswarareddyI.T.AnalystMCITP(70-451) Danny Ocean Hall of Fame Group: General Forum Members Points: 3410 Visits: 1549 Really a good question with good explanation. ThanksVinay Kumar-----------------------------------------------------------------Keep Learning - Keep Growing !!!www.GrowWithSql.com UMG Developer SSCertifiable Group: General Forum Members Points: 7286 Visits: 2204 Great question, and a very understandable explanation. Thanks! rals SSCrazy Group: General Forum Members Points: 2507 Visits: 844 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? Regards,Rals. ziangij SSCarpal Tunnel Group: General Forum Members Points: 4504 Visits: 380 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,Crajesh.subramanian (6/25/2010)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? Christian Buettner-167247 SSCrazy Eights Group: General Forum Members Points: 8557 Visits: 3889 rajesh.subramanian (6/25/2010)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?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 SSCrazy Eights Group: General Forum Members Points: 8557 Visits: 3889 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,CThere 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-Insane Group: General Forum Members Points: 22182 Visits: 8519 Damn, confused me, went with A,B,C :-P Forever trying to learnFor 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/If you litter your database queries with nolock query hints, are you aware of the side effects?Try reading a few of these links...(*) Missing rows with nolock(*) Allocation order scans with nolock(*) Consistency issues with nolock(*) Transient Corruption Errors in SQL Server error log caused by nolock(*) Dirty reads, read errors, reading rows twice and missing rows with nolockCraig Wilkinson - Software EngineerLinkedIn ziangij SSCarpal Tunnel Group: General Forum Members Points: 4504 Visits: 380 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,CThere 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,'')