Solved

Benefit of STUFF verseus coalesce

Posted on 2013-01-30
3
1,649 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

760 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

18 Experts available now in Live!

Get 1:1 Help Now