Database Programming: A New String Concatenation Contender for SQL Server 2008

re: Database Programming: A New String Concatenation Contender for SQL Server 2008

Claudio Cauchi — Wed, 16 Jun 2010 10:53:13 GMT

Answering my own question: [Promise I had googled this a zillion times]

from: sqlblog.com/.../handling-special-characters-with-for-xml-path.aspx

select

stuff(

(select ', <' + name + '>'

from sys.databases

where database_id > 4

order by name

for xml path(''), type

).value('(./text())[1]','varchar(max)')

, 1, 2, '') as namelist;

re: Database Programming: A New String Concatenation Contender for SQL Server 2008

Claudio Cauchi — Wed, 16 Jun 2010 10:27:35 GMT

I know I might be resuscitating an old post however I would like to pose a problem I find to you guys. I prefer the FOR XML approach when concatenating strings. However my data sometimes contains non-xml friendly characters like &, > and < which are translated to & etc. Is there a neat way to avoid this from happening? I would like to avoid the REPLACE (result,'&','&') as there are multiple "special" characters.

http://blog.sqlauthority.com/2009/09/20/sql-server-execution-plan-and-results-of-aggregate-concatenation-queries-depend-upon-expression-location/

TrackBack — Tue, 29 Sep 2009 03:32:44 GMT

re: Database Programming: A New String Concatenation Contender for SQL Server 2008

WardPond — Tue, 08 Sep 2009 06:09:39 GMT

@Adam: you've obviously got way more context around the history of this issue that I do. Under the circimstances, I agrree that the XML variant is unambiguously preferable for ordered concatenation.

Now.. how 'bout them row constructors? :)

-wp

re: Database Programming: A New String Concatenation Contender for SQL Server 2008

Adam Machanic — Tue, 08 Sep 2009 04:27:05 GMT

Hi Ward,

I have no issue w/ compound operators--I think they're a useful addition to T-SQL and see no reason to avoid them (unless you have to write code that has any chance of being back-ported to 2005, etc). The aggregate concatenation trick is, in my opinion, not something that should be used unless the QP team decides to modify the query engine to fully and deterministically support it. The issue, as far as I know, is not limited to cases where there is a function used in the ORDER BY--that's just an easy way to show the problem. There is simply no guarantee that it will work.

And will the issue indeed be fixed eventually? I don't know, but I certainly don't see that on the horizon; this "feature" has been heavily discussed since the SQL Server 7.0 time frame. I've been an MVP since 2004 and have seen numerous discussions on the topic since that time in the private MVP newsgroup--many of which included members of the QO and QP teams--and never has there been any indication that anyone was planning to fix the issue. I'm sure there were many discussions before I showed up. So this is something they've known about for 10+ years and four major versions. I believe that the primary argument against a fix is that it's not a "fix" at all since this behavior is undocumented. So there's no bug; a "fix" would actually be an enhancement. And with the supported and much more flexible FOR XML PATH workaround, plus the ability to do aggregate concatenation using SQLCLR, there is no need for such an enhancement.

re: Database Programming: A New String Concatenation Contender for SQL Server 2008

WardPond — Tue, 08 Sep 2009 04:13:24 GMT

@Adam: As always, thanks for your comments, no matter how you format them. I've updated the top of the post with what I hope is an accurate synopsis of your concerns.

If I'm reading the KB article correctly, the bug only manifests itself when functions are used on columns in the scope of the ORDER BY statement. This approach wouldn't be a best practice (we couldn't use the order of an index to satisfy an ORDER BY; even an index on the necessary column would need to be scanned due to the presence of the functions), and there are several workaround (in addition to the workaround discussed in the article, an index on a calculated, persisted column matching the expression would also solve the problem if that column were used in the ORDER BY).

In the context of the original post, all of this likely mitigates in favor of use of the XML variant for string concatenation, at least until the ORDER BY issue is resolved.

It seems to me that compound operators are still useful for scenarios where order is immaterial (aggregates, etc., although alternate syntax certainly also exists in these cases).

Do you agree, Adam, or do you stay away from compound operators altogether?

-wp

re: Database Programming: A New String Concatenation Contender for SQL Server 2008

Adam Machanic — Tue, 08 Sep 2009 03:00:51 GMT

... and sorry for the three posts, but I just tested and this is certainly not fixed in SQL Server 2008, even with the new syntax... try the following with and without the ORDER BY (and with different ORDER BYs):

DECLARE @temp table([id] int);

INSERT INTO @temp VALUES(3);

INSERT INTO @temp VALUES(2);

INSERT INTO @temp VALUES(1);

DECLARE @i varchar(50);

SET @i = '';

SELECT

@i += convert(varchar, [id])

FROM @temp

ORDER BY

LTRIM(RTRIM([id]));

PRINT @i;

re: Database Programming: A New String Concatenation Contender for SQL Server 2008

Adam Machanic — Tue, 08 Sep 2009 02:56:01 GMT

I did the Googling for you. Found a KB article on the topic, describing the ORDER BY issue:

http://support.microsoft.com/default.aspx?scid=287515

re: Database Programming: A New String Concatenation Contender for SQL Server 2008

Adam Machanic — Tue, 08 Sep 2009 02:53:43 GMT

Hi Ward,

Nothing really "new" here except the syntax. We call that the "aggregate concatenation" trick. Google and you'll see lots of stuff. Look especially for posts by Steve Kass. What you'll find is that the behavior is undefined and your results are not guaranteed, by the query processor, to comply to what you specified in the ORDER BY clause.

The order IS, in fact, guaranteed when using FOR XML, the behavior of that operator is documented and well-defined, and in my tests they're equally fast. So, no contest if you ask me.