THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Not-so-dirty SQL hacks

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Tuesday, September 10, 2013 12:24 PM by Rob Farley



JDS said:

I have always done something like this below for quick string concatenation. Do you prefer the XML based approach upon performance or some other criteria?


           FROM    tempdb.dbo.sysobjects

           WHERE   ID = OBJECT_ID(N'tempdb..#source') )


   DROP TABLE #source


CREATE TABLE #source (

col1 CHAR(1)




 VALUES  ('a')

, ('b')

, ('c')

, ('d')

, ('e')

, ('f')

, ('g')


String Concatenation



SELECT @string = ISNULL(@string,'') + ',' + col1

FROM #source s

SELECT @string

September 12, 2013 9:13 AM

Rob Farley said:

Based on the fact that it's not supported.

Based on the fact that it can be affected by non-clustered indexes, parallelism, and other good things, giving 'incorrect' results.

Based on the fact that it's procedural and has no opportunity to be handled by the QO in a better way.

Based on the fact that you can't order the data how you like without risking the results.

If you must use code that moves through a result set in order like that, you should use a cursor. And that's saying something!!

September 12, 2013 9:25 AM

merrillaldrich said:

I'm crazy and self-destructive, I guess. I know the performance advantages. I get the reasoning.

AND ... still I cringe when I see this XML concatenation trick. It's my childish idealism.

Still, great explanation of how to do it :-).

September 12, 2013 12:00 PM

Rob Farley said:

How do you handle it then, Merrill?

September 12, 2013 4:50 PM

Josías Hernández said:

It is just AWESOME !!! I really enjoy it !!! Very smart the solution!!!

Thanks for the trick Rob.

September 16, 2013 11:32 AM

Allan S. Hansen said:

I've used XML for string concat in SQL Server for a couple of years now, and it's always felt 'hacky' - but - yes, it really does the trick.

I rarely - however - put it into production code other than data migration or imports which aren't that time sensitive.

In other situations, I'd properly rather handle it with a combination of database/code logic as string concatenation often is connection to a business logic problem needing to be solved, more than an actual data problem.

September 17, 2013 1:52 AM

merrillaldrich said:

Rob - I don't have to code much. We buy everything. If I had to code this I would probably cringe and write it as you show here. It's become sort of a best practice, but it's still not pretty :-).

September 17, 2013 7:49 PM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG

Privacy Statement