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


"SELECT @local_variable"


"SELECT @local_variable"

Author
Message
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: 64053 Visits: 9671
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.
antony-688446
antony-688446
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 371
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!
VM-723206
VM-723206
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 Visits: 267
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?
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25096 Visits: 12484
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

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63609 Visits: 18570
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

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