SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"SELECT @local_variable"


"SELECT @local_variable"

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18301 Visits: 12426
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
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64463 Visits: 9671
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.
OCTom
OCTom
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4061 Visits: 4152
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!
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7640 Visits: 2629
Good question. Took a while to puzzle it out. Thanks.
sknox
sknox
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3920 Visits: 2920
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.
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5607 Visits: 1619
Huh..........Still a lot to learn. Trying to understand the programming puzzle.

Good question, though.

SQL DBA.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18301 Visits: 12426
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
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3633 Visits: 1323
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/



Mauve
Mauve
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3190 Visits: 2065
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.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18301 Visits: 12426
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search