Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 "SELECT @local_variable" Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 24, 2010 8:26 PM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 2:28 AM Points: 3,369, Visits: 3,191
Post #942789
 Posted Thursday, June 24, 2010 9:42 PM
 SSCommitted Group: General Forum Members Last Login: Wednesday, July 29, 2015 11:50 PM Points: 1,967, Visits: 1,189
 good question. but i also expected that result is A,B,C. MalleswarareddyI.T.AnalystMCITP(70-451)
Post #942797
 Posted Thursday, June 24, 2010 10:56 PM
 Ten Centuries Group: General Forum Members Last Login: Thursday, July 23, 2015 2:40 AM Points: 1,242, Visits: 1,549
 Really a good question with good explanation. ThanksVinay Kumar-----------------------------------------------------------------Keep Learning - Keep Growing !!!www.GrowWithSql.com
Post #942806
 Posted Thursday, June 24, 2010 11:28 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, June 30, 2015 9:47 AM Points: 2,160, Visits: 2,204
 Great question, and a very understandable explanation. Thanks!
Post #942808
 Posted Friday, June 25, 2010 12:56 AM
 SSCrazy Group: General Forum Members Last Login: Friday, March 4, 2016 7:14 AM Points: 2,130, Visits: 834
 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.
Post #942830
 Posted Friday, June 25, 2010 1:08 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 19, 2016 5:42 AM Points: 2,830, Visits: 374
 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?
Post #942838
 Posted Friday, June 25, 2010 1:29 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 1, 2015 6:09 AM Points: 2,891, Visits: 3,889
 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
Post #942846
 Posted Friday, June 25, 2010 1:49 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 1, 2015 6:09 AM Points: 2,891, Visits: 3,889
 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
Post #942859
 Posted Friday, June 25, 2010 2:15 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 8:35 AM Points: 2,492, Visits: 8,430
 Damn, confused me, went with A,B,C 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
Post #942877
 Posted Friday, June 25, 2010 2:36 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 19, 2016 5:42 AM Points: 2,830, Visits: 374
 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,'')
Post #942887

 Permissions