THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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: http://blogs.lobsterpot.com.au/2013/09/10/not-so-dirty-sql-hacks/

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

Comments

 

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?

IF EXISTS ( SELECT  *

           FROM    tempdb.dbo.sysobjects

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

BEGIN

   DROP TABLE #source

END

CREATE TABLE #source (

col1 CHAR(1)

)

INSERT INTO #source

       (col1)

 VALUES  ('a')

, ('b')

, ('c')

, ('d')

, ('e')

, ('f')

, ('g')

/*******************

String Concatenation

********************/

DECLARE @string VARCHAR(MAX)

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

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement