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 «««56789

A simple T-SQL statement to create a list of lookup values Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 5:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:49 PM
Points: 6,098, Visits: 8,364
BowlOfCereal (9/28/2012)
There's been a lot of discussion in this (oddly) revived thread today.

The reason for the revival of the thread is that the article was prominently featured in the newsletter.

According to Microsoft's KB article and Connect comments: the unpredictable, unsupported behavior occurs "when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries". I understand that (or at least I think I do). But this has nothing to do with building a comma delimited string. The original article said nothing about applying operators to an ORDER BY clause.

So, my question: is it true that the method presented in this article is "known to potentially return incorrect results"?

From the relevant Microsoft Knowledge Base article: "The correct behavior for an aggregate concatenation query is undefined."
If the correct behaviour is undefined, then it's impopssible to tell if a given result is incorrect. Ergo, the method presented in this article can by definition never return "incorrect" results.

However, the results returned may be completely different than what you expect, or want. For instance, when the optimizer chooses to use a parallel plan, it could just return the result from one of the threads, which you would probably consider incorrect. I don't think that the optimizer will at this time choose a parallel plan for queries of this type (I just spent a half hour trying very hard to get it to, but failed) - but since this is undocumented behaviour of the optimizer, that might change. And if a future change to the optimizer causes it to create a parallel plan for this query, your bug reports will probably be closed as "by design" - since "the correct behavior for an aggregate concatenation query is undefined."

For me, this is enough reason to avoid this method. The XML method is a perfect replacement - and this method IS documented, and hence guaranteed.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1366136
Posted Friday, September 28, 2012 5:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:13 PM
Points: 73, Visits: 402
Hugo Kornelis (9/28/2012)


However, the results returned may be completely different than what you expect, or want. For instance, when the optimizer chooses to use a parallel plan, it could just return the result from one of the threads, which you would probably consider incorrect. I don't think that the optimizer will at this time choose a parallel plan for queries of this type (I just spent a half hour trying very hard to get it to, but failed) - but since this is undocumented behaviour of the optimizer, that might change. And if a future change to the optimizer causes it to create a parallel plan for this query, your bug reports will probably be closed as "by design" - since "the correct behavior for an aggregate concatenation query is undefined."

For me, this is enough reason to avoid this method. The XML method is a perfect replacement - and this method IS documented, and hence guaranteed.


Thanks for the reply; these are excellent points. I still read the KB article as specifically having to do with the ORDER BY issue, but I'm coming around to seeing your larger point. Today, the ORDER BY issue may be the only way to expose the problem with the "aggregate concatenation query" approach, but there's no guarantee that will be true tomorrow.

And as you point out, given that there's a perfectly sound alternative, we'd all certainly be wise to explore the XML method. That will improve my future code; whether I get time to refactor my existing stuff is another matter.
Post #1366141
Posted Saturday, September 29, 2012 6:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, September 29, 2012 6:38 AM
Points: 276, Visits: 21
Hello.

I'm used this one, without cursor:

declare @sep varchar(1)
declare @text varchar(max)
set @sep=''

select @text=@text + @sep + column1, @sep=',' from table

Modification for distinct version was shown: replace table by (select distinct column1 from table) as t


regards
Jarek
Post #1366183
Posted Saturday, September 29, 2012 12:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:22 AM
Points: 12, Visits: 88
I just want to say thanks, I can use this to get rid of several while loops.
Post #1366209
Posted Saturday, September 29, 2012 1:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, January 25, 2014 2:00 AM
Points: 62, Visits: 119
can do by for xml element . and then eliminate traling ',' with stuff.
no need of a variavle to store.direcectly can return that.
Post #1366215
Posted Tuesday, October 2, 2012 1:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, August 2, 2014 8:54 AM
Points: 17, Visits: 238
This link has got a good compilation of the possible ways to do it.

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post.
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia
Post #1366874
Posted Saturday, October 6, 2012 9:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 6, 2014 7:42 PM
Points: 9, Visits: 35
Definitely use XML over the variable-assignment syntax, as SQL Server supports XML. The variable-assignment syntax is not documented. It works, but is not supported.
Post #1369438
Posted Tuesday, December 25, 2012 8:29 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55
Nice article.
Post #1400068
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse