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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



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

Leave a Comment


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