June 25, 2010 at 5:45 pm
Hugo Kornelis (6/25/2010)
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!
No apologies necessary... just funny to see it happen ;-):hehe:.
June 27, 2010 at 4:00 pm
Mauve (6/25/2010)
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.
No, you can use the FOR XML PATH method, example given on page 2 of discussion!
June 29, 2010 at 12:49 am
I'm not sure why DISTINCT was required here..
SELECT @t = @t + @comma + a.a ,@comma = ','
FROM #a a ORDER BY 1
The above select returns a C as well. Am I missing something here?
June 30, 2010 at 9:47 am
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".
Yes, both those statements are true, but it's a fun question anyway. And the discussion will probably be lively and interesting.
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!!!!
Microsoft do NOT state that this technique is unsupported. They state that the result is undefined in some circumstances, and document a workaround that supposedly avoids those circumstances. The workaround is: "In order to achieve the expected results from an aggregate concatenation query, apply any Transact-SQL function or expression to the columns in the SELECT list rather than in the ORDER BY clause."
So I will continue to use SELECT @LV to compute aggregate concatenations (and to compute reductions with functions other than string concatenation) in production code. Having read several MS articles on this topic, and being thoroughly unconvinced that the workaround suggested by MS is effective in 100% of cases in all releases of SQL Server (and noting anyway that MS misunderstand the interaction of the ANSI spec of ORDER BY with their own specification of SELECT @LV), I apply a rather tighter restriction: I will not use them with an order by clause or a distinct qualifier (since distinct implies a sort), and I will always use a maxdop hint to restrict parallelism, unless the function in question is both commutative and associative (in which case order by would harmless but I still wouldn't use it because it would also be pointless, and maxdop is irrelevant; distinct still has to be avoided unless the base function has further properties that make it safe). For anything non-trivial I will also include in the production system extra code to check that each aggregate concatenation works as required, just in case MS changes something (I've had enough experience of things stopping working because SQL tightened something up somewhere to be careful about this). The reason for this is that I don't have unbounded computational resources, so I won't use a cursor instead, nor use grossly slow XML parsing, and that leaves me only with aggregate concatenation if I want to efficiently evaluate a reduction outside the hopelessly restricted set provided as built in aggregates in SQL. If this technique does stop working that will be in the future (it's worked in every SQL Server release from 7 to now) when I hope that (a) there will be more computational power (including more data moving power) and that (b) SQL will have moved on to provide a better method of doing this.
There may be some interesting variations on the SELECT @LV theme in the MERGE statement. BOL http://msdn.microsoft.com/en-gb/library/bb510625(SQL.100).aspx provides a syntax for the <set_clause> which explicitly includes "@variable = column = expression" but then expressly states "Setting a variable to the same value as a column is not permitted" - what can that mean? Of course "@variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression" is also specified as allowed in the set clause. Is there any MS documentation on using MERGE to do "aggregate concatenations"? (I haven't seen any.) Maybe SELECT @LV will be completely safe in MERGE because there is no ORDER BY clause? I haven't done any experimentation to find out.
One of these days SQL will jump out of it's archaic and primitive world view that avoids catering explicitly for a REDUCE operation and provides instead a limited number of "aggregates" which provide reduction for only a very limited set of elementary functions. In the mean time we are stuck with something less than ideal. Having worked in the 80s and early 90s with people who were active in SQL standardisation and seen the amazing slowness of progress I imagine it will be one of the big software companies that defines the improvement, not the standards bodies.
Tom
July 7, 2010 at 3:00 pm
Good question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply