Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

"SELECT @local_variable" Expand / Collapse
Author
Message
Posted Thursday, June 24, 2010 8:26 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 2,586, Visits: 2,442
Comments posted to this topic are about the item "SELECT @local_variable"
Post #942789
Posted Thursday, June 24, 2010 9:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 13, 2014 10:52 PM
Points: 1,940, Visits: 1,170
good question. but i also expected that result is A,B,C.



Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #942797
Posted Thursday, June 24, 2010 10:56 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Really a good question with good explanation.



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #942806
Posted Thursday, June 24, 2010 11:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Great question, and a very understandable explanation. Thanks!
Post #942808
Posted Friday, June 25, 2010 12:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 24, 2014 2:51 PM
Points: 1,913, Visits: 795
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?


Regards,
Rals
.
Post #942830
Posted Friday, June 25, 2010 1:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:03 PM
Points: 2,098, Visits: 371
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?
Post #942838
Posted Friday, June 25, 2010 1:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #942846
Posted Friday, June 25, 2010 1:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #942859
Posted Friday, June 25, 2010 2:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:07 AM
Points: 2,386, Visits: 7,609
Damn, confused me, went with A,B,C


Not a DBA, just trying to learn

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/



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 nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #942877
Posted Friday, June 25, 2010 2:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:03 PM
Points: 2,098, Visits: 371
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,'')
Post #942887
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse