Solved

Benefit of STUFF verseus coalesce

Posted on 2013-01-30
3
1,687 Views
Last Modified: 2013-01-31
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

0
Comment
Question by:lrbrister
3 Comments
 
LVL 18

Accepted Solution

by:
deighton earned 250 total points
ID: 38835928
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 38835958
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
 

Author Closing Comment

by:lrbrister
ID: 38839681
Thanks guys.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now