Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Carlo Romagnano » "SELECT @local_variable"
Add to briefcase
35 posts, Page 2 of 4
««1234»»»

"SELECT @local_variable" Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Hugo Kornelis
Posted Friday, June 25, 2010 3:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 5,244, Visits: 7,063
Though the explanation sounds enlightening, it is wrong. And it is also wrong to claim that the result of this query will always be "C".

Though many people seem to prefer to ignore it, it is widely known that this method of string concatenation is NOT SUPPORTED. Microsoft itself writes (I believe in a Knowledge Base article; I've spent some time trying to hunt it down but failed, unfortunately) that this method is not supported and that the results are unpredictable.

As far as I am concerned, any of the alternatives mentioned may be returned, depending on version, SP level and build of SQL Server, hardware used, and amount of other activity on the server.

Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!!

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #942899
Ninja's_RGR'us
Posted Friday, June 25, 2010 4:55 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 21,359, Visits: 9,543
Hugo Kornelis (6/25/2010)
Though the explanation sounds enlightening, it is wrong. And it is also wrong to claim that the result of this query will always be "C".

Though many people seem to prefer to ignore it, it is widely known that this method of string concatenation is NOT SUPPORTED. Microsoft itself writes (I believe in a Knowledge Base article; I've spent some time trying to hunt it down but failed, unfortunately) that this method is not supported and that the results are unpredictable.

As far as I am concerned, any of the alternatives mentioned may be returned, depending on version, SP level and build of SQL Server, hardware used, and amount of other activity on the server.

Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!!


Here, here. I instinctively selected a,b,c and low and behold that's what I got on my sql 2008 machine after answering "wrong"

Here's a bug I sent on connect with a very similar technique. I'll paste the ms reply right after the link

https://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition



Microsoft somehwere in 2008

Thanks for your feedback. The behavior you are seeing is by design. Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior. This can change from release to release or even within a particular server version due to changes in the query plan. You cannot rely on this behavior even if there are workarounds. See the below KB article for more details:

http://support.microsoft.com/kb/287515

The ONLY guaranteed mechanism are the following:

1. Use cursor to loop through the rows in specific order and concatenate the values
2. Use for xml query with ORDER BY to generate the concatenated values
3. Use CLR aggregate (this will not work with ORDER BY clause)

--
Umachandar, SQL Programmability Team


Here's the original question I asked on ssc which lead to the connect being filled.

http://www.sqlservercentral.com/Forums/Topic607455-145-1.aspx


I won't post the workarounds since they are not supposed to work by design.
Post #942948
OCTom
Posted Friday, June 25, 2010 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 2,018, Visits: 2,852
An unsupported technique? I have seen this used and have been encouraged to use this technique by an experienced consulted. I'll be sending him the link to this discussion. It behaves illogically (at least to me). I expected A,B,C to be the result. I haven't run it but find it disturbing that it would be inconsistent between versions, releases, whatever BY DESIGN!
Post #943008
Daniel Bowlin
Posted Friday, June 25, 2010 6:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:10 PM
Points: 2,673, Visits: 2,418
Good question. Took a while to puzzle it out. Thanks.
Post #943014
sknox
Posted Friday, June 25, 2010 7:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:31 AM
Points: 1,041, Visits: 1,356
OCTom (6/25/2010)
An unsupported technique? I have seen this used and have been encouraged to use this technique by an experienced consulted. I'll be sending him the link to this discussion. It behaves illogically (at least to me). I expected A,B,C to be the result. I haven't run it but find it disturbing that it would be inconsistent between versions, releases, whatever BY DESIGN!


You have to understand Microsoft's definition of "by design". It doesn't solely mean that they actively design the software to act in that way. It can also mean (as in this case) that they design the software to work to a specificiation, and since this use is outside the scope of that specification, they don't ever test for consistent results for it.
Post #943033
SanjayAttray
Posted Friday, June 25, 2010 7:41 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
Huh..........Still a lot to learn. Trying to understand the programming puzzle.

Good question, though.

SQL DBA.
Post #943039
Hugo Kornelis
Posted Friday, June 25, 2010 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 5,244, Visits: 7,063
OCTom (6/25/2010)
An unsupported technique? I have seen this used and have been encouraged to use this technique by an experienced consulted. I'll be sending him the link to this discussion. It behaves illogically (at least to me). I expected A,B,C to be the result. I haven't run it but find it disturbing that it would be inconsistent between versions, releases, whatever BY DESIGN!

If a consultant would ever try that on me, I'd fire him on the spot, and tell him what he can do with the bill for his hours worked.

Yes, it does work (*). Usually. Maybe even about 99.9% of the times. Is 0.1% chance of incorrect results acceptable for your user?

(*) Except, of course, in those cases where it does not work. Usually. Such as in this question.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #943051
kevin.l.williams
Posted Friday, June 25, 2010 8:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:52 AM
Points: 2,796, Visits: 1,125
Hugo Kornelis (6/25/2010) Is 0.1% chance of incorrect results acceptable for your user?
Not for me.

Hugo, is this the article you were looking for?

"The correct behavior for an aggregate concatenation query is undefined."

http://support.microsoft.com/kb/q287515/


Post #943097
Mauve
Posted Friday, June 25, 2010 9:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 10:43 AM
Points: 1,146, Visits: 1,848
Hugo is 100% correct! Using this technique is 100% unpredictable. It will not work with table variables, derived tables, etc.

If you need to make a delimited list of values use XML.

Example:
The following code will create a semi-colon delimited list of user names where "NAME" contains names in the format of "LastName, FirstName".

SELECT STUFF(V.DELIMITED_LIST, 1, 2, N'')
  FROM
    (
      SELECT
       N'; ' + U.NAME AS "text()"
      FROM
       USERS U
      WHERE U.NAME IS NOT NULL -- exclude any NULL values
        AND U.NAME <> N'' -- exclude any zero-length strings
      ORDER BY 1
      FOR XML PATH(N'')
    ) AS V (DELIMITED_LIST)

(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #943117
Hugo Kornelis
Posted Friday, June 25, 2010 9:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 5,244, Visits: 7,063
kevin.l.williams (6/25/2010)
Hugo Kornelis (6/25/2010) Is 0.1% chance of incorrect results acceptable for your user?
Not for me.

Hugo, is this the article you were looking for?

"The correct behavior for an aggregate concatenation query is undefined."

http://support.microsoft.com/kb/q287515/

EXACTLY!
Thanks for locating it, Kevin. That was exactly the article I had in mind.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #943142
« Prev Topic | Next Topic »

Add to briefcase
35 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.