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
 SSCrazy Group: General Forum Members Last Login: Today @ 5:37 AM Points: 2,225, Visits: 2,120
Post #942789
 Posted Thursday, June 24, 2010 9:42 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 5:00 AM Points: 1,917, Visits: 1,130
 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: Tuesday, December 03, 2013 2:28 AM Points: 1,239, Visits: 1,541
 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: Thursday, November 07, 2013 10:01 AM Points: 2,163, Visits: 2,181
 Great question, and a very understandable explanation. Thanks!
Post #942808
 Posted Friday, June 25, 2010 12:56 AM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 20, 2013 4:08 AM Points: 1,830, Visits: 775
 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
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 06, 2013 2:53 AM Points: 1,713, Visits: 367
 since there is only one column, why is this working as well ? SELECT @t = @t + @comma + a.a ,@comma = ',' FROM #a a ORDER BY 2Result : 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: Today @ 12:47 AM Points: 2,723, Visits: 3,792
 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: Today @ 12:47 AM Points: 2,723, Visits: 3,792
 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 2Result : 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: Today @ 9:01 AM Points: 2,358, Visits: 7,141
 Damn, confused me, went with A,B,C Not a DBA, just 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 nolockLinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #942877
 Posted Friday, June 25, 2010 2:36 AM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 06, 2013 2:53 AM Points: 1,713, Visits: 367
 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 2Result : 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