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 10:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:13 AM
Points: 436, Visits: 322
Why is the answer 'C'? Could it be possible that the query would return 'A' or 'B'?
Post #943178
Posted Friday, June 25, 2010 10:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 6,018, Visits: 8,287
JF1081 (6/25/2010)
Why is the answer 'C'? Could it be possible that the query would return 'A' or 'B'?

Possible? I'd say yes, because the results of this kind of query are undefined (see the previous page if this disussion).
Probable? No, not really. For the reasons mentioned in the explanation of the question.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #943188
Posted Friday, June 25, 2010 11:42 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
Hugo Kornelis (6/25/2010)
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.



I guess someone forgot to read my post
Post #943235
Posted Friday, June 25, 2010 12:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 6,018, Visits: 8,287
Ninja's_RGR'us (6/25/2010)
Hugo Kornelis (6/25/2010)
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.



I guess someone forgot to read my post

I did read your post, but the Connect item you linked to was not what I meant when I wrote that I knew this to be documented by MS. The MSKB article Kevin found was.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #943247
Posted Friday, June 25, 2010 12:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:41 AM
Points: 77, Visits: 218
Yet I can't see why the assignation concatenates the strings in one case and does not in the other. Somewhere I read the concatenation behavior is not assured, or is not documented. Maybe is so because of this strange effect.
Post #943251
Posted Friday, June 25, 2010 12:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Hugo KornelisI did read your post, but the Connect item you linked to was not what I meant when I wrote that I knew this to be documented by MS. The MSKB article Kevin found was.


You missed that Ninja's post, and the Connect item, had a link to the same KB article...
Post #943252
Posted Friday, June 25, 2010 12:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Hugo Kornelis (6/25/2010)Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!!


I agree, but I think variable concatenation is very commonly used, so it is important for people to understand that the ORDER BY clause can really cause you grief. (I think this relates to the running total examples that use local variables.) Personally I prefer using FOR XML to concatenate strings, but there are cases where it doesn't work. (Special characters as one example.)
Post #943255
Posted Friday, June 25, 2010 12:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 6,018, Visits: 8,287
UMG Developer (6/25/2010)
Hugo KornelisI did read your post, but the Connect item you linked to was not what I meant when I wrote that I knew this to be documented by MS. The MSKB article Kevin found was.


You missed that Ninja's post, and the Connect item, had a link to the same KB article...


You are right, I missed that. And not jsut once, but twice. Because I went back and reread Ninja's post when he mentioned being overlooked, and STILL missed the reference.

My apologies, Ninja!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #943259
Posted Friday, June 25, 2010 12:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:08 PM
Points: 1,186, Visits: 1,976
UMG Developer (6/25/2010)
Hugo Kornelis (6/25/2010)Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!!


I agree, but I think variable concatenation is very commonly used, so it is important for people to understand that the ORDER BY clause can really cause you grief. (I think this relates to the running total examples that use local variables.) Personally I prefer using FOR XML to concatenate strings, but there are cases where it doesn't work. (Special characters as one example.)

Then the only proper method is to use a cursor, which will guarantee the result set, and concatenate the values returned by the cursor into the desired string.



(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 #943266
Posted Friday, June 25, 2010 12:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 4:21 PM
Points: 41, Visits: 71
Wow. That was an excellent question that illustrates how important those query plans are and the problem of intermixing a declarative and procedural elements into queries.
Post #943289
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse