Benefit of STUFF verseus coalesce

I saw an article a while back comparing the speed of STUFF versus coalesce.

Can someone give me a quick reason why its actually supposed to be better?

Select *,STUFF(( SELECT ' ' + CC.[First Name] + ' ' + CC.[Last Name] + '-' + B.Notes   + CHAR(10)                    

                  FROM  dbo.[Client Contact Events] AS B

                  INNER JOIN dbo.[Client Contacts] CC

                  ON B.[Contact ID] = CC.[Contact ID]

                  WHERE [CVT].[Client ID] = B.[Client ID]

                        AND   B.EventType = 'Visit'

                        AND   B.[Contact Date] > [CVT].createdDate  FOR XML PATH ('')),1,0,'') AS [Contact Notes]
From mytable

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
deightonConnect With a Mentor Commented:
https://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx

I don't know about speed, but here is an article talking about the creation of a concatenated
string using FOR XML PATH rather than

DECLARE @X varchar(200);
SELECT @X = COALESCE(@X , '') + YourField FROM YourTable;
SELECT @X;



which can go wrong if you introduce for example

DECLARE @X varchar(200);
SELECT @X = COALESCE(@X , '') + YourField FROM YourTable ORDER BY LEN(YourField);
SELECT @X;

-----

however STUFF and COALESCE seem to be asides here, they are only being used to tidy things up.  It is really an argument between the 'SELECT @VARIABLE = ..'  method and 'FOR XML PATH'

personally I think 'FOR XML PATH' has problems
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
STUFF and COALESCE do two different things, so you can't really make a direct comparison between them.

I use only FOR XML PATH, since there are documented issues with "SELECT @variable =", particularly with parallelism, and MS has explicitly stated that it's not supported.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.