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 Friday, June 25, 2010 3:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 5,793, Visits: 8,003
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
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: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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
Posted Friday, June 25, 2010 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 2,409, Visits: 3,435
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
Posted Friday, June 25, 2010 6:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
Good question. Took a while to puzzle it out. Thanks.
Post #943014
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: Yesterday @ 6:34 AM
Points: 1,243, Visits: 1,591
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
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, April 11, 2014 12:29 PM
Points: 3,924, Visits: 1,586
Huh..........Still a lot to learn. Trying to understand the programming puzzle.

Good question, though.


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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 5,793, Visits: 8,003
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
Posted Friday, June 25, 2010 8:46 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 28, 2014 11:01 AM
Points: 3,047, Visits: 1,229
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
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, April 07, 2014 8:53 AM
Points: 1,182, Visits: 1,949
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
Posted Friday, June 25, 2010 9:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 5,793, Visits: 8,003
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 ««1234»»»

Permissions Expand / Collapse