Solved

Benefit of STUFF verseus coalesce

Posted on 2013-01-30
3
1,758 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:Scott Pletcher
Scott Pletcher 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
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 setup several different housekeeping processes for a SQL Server.

808 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