Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A simple T-SQL statement to create a list of lookup values


A simple T-SQL statement to create a list of lookup values

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8339 Visits: 11584
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
BowlOfCereal
BowlOfCereal
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 454
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. Hehe
stoklosa
stoklosa
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 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
King Conch
King Conch
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 97
I just want to say thanks, I can use this to get rid of several while loops.
krushna_deb
krushna_deb
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 125
can do by for xml element . and then eliminate traling ',' with stuff.
no need of a variavle to store.direcectly can return that.
vinaypugalia
vinaypugalia
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 268
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
Bonz99
Bonz99
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 41
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.
Neha05
Neha05
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 Visits: 60
Nice article.
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