Use COALESCE to get a separated list?

Sometimes you are looking for an easier solution then you are used to. Today i had one in that category. Nothing new, but simple and effective. But while writing this post i came up something quite cool which can answer almost any question i had in SQL Server coding best practices. They are called TRACE FLAGS. An Undocumented feature of SQL Server. You can read all about them in a very good post from Benjamin Nevarez, called “http://www.benjaminnevarez.com/2012/04/more-undocumented-query-optimizer-trace-flags/“. Anyways, i used the 8605 traceflag on my examples below, and i came to a very strange result. I was trying to tell that using a COALESCE function to generated a semi-column separated list would be better then to use the old string concatenation way. But then….

The COALESCE function.
An easy solution to get rid off null values, but also to create separated lists is the COALESCE function . In this case i needed a semi-column separated list, which is quite easy to do.

Look at the following query. This is the way i used to create semi-column separated lists.

declare @uniqueDates varchar(max)
set @uniqueDates = ''
select  @uniqueDates = @uniqueDates 
          + ';' + convert(varchar(10), TransactionDate, 105)
from    AdventureWorks2008R2.Production.TransactionHistory
group by TransactionDate

set @uniqueDates = SUBSTRING(@uniqueDates, 2, len(@uniqueDates))
select @uniqueDates

It will output 1 value, which is a concatenation of the unique TransactionDates, separated with a semi-column. When you look at the query you’ll see that the semi-column is always added after the previous value. So that means that our output will start with a semi-column. Something that we don’t want, so therefor we do a substring on the value to remove the first char.

I have done this so many times in the past years, and heey, why change something when it isn’t broke? Well.. i like to evolve in things, so does my code!

A simple solution is to use the COALESCE function. Look at the following script.

declare @uniqueDates varchar(max)
set @uniqueDates = ''

select  @uniqueDates = coalesce(@uniqueDates + ';', '')
          + convert(varchar(10), TransactionDate, 105)
from    AdventureWorks2008R2.Production.TransactionHistory
group by TransactionDate

select @uniqueDates

It almost looks the same, however, we don’t have to do the substring anymore to sanitize our output.

When we compare those outputs of the traceflags we see what is going on on a little deeper level. Lets look at the trace flag output of the coalesce function :

 
         AncOp_PrjEl COL: Expr1003 

                ScaOp_Arithmetic x_aopAdd

                    ScaOp_IIF varchar(max) collate 872468488,Null,Var,Trim,ML=65535

                        ScaOp_Comp x_cmpIsNot

                            ScaOp_Arithmetic x_aopAdd

                                ScaOp_Identifier COL: @uniqueDates 

                                ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=1) XVAR(varchar,Owned,Value=Len,Data = (1,;))

                            ScaOp_Const TI(varchar collate 872468488,Null,Var,Trim,ML=1) XVAR(varchar,Not Owned,Value=NULL)

                        ScaOp_Arithmetic x_aopAdd

                            ScaOp_Identifier COL: @uniqueDates 

                            ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=1) XVAR(varchar,Not Owned,Value=Len,Data = (1,;))

                        ScaOp_Convert varchar(max) collate 872468488,Null,Var,Trim,ML=65535

                            ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=1) XVAR(varchar,Not Owned,Value=Len,Data = (0,))

                    ScaOp_Convert varchar collate 872468488,Null,Var,Trim,ML=10

                        ScaOp_Identifier QCOL: [AdventureWorks2008R2].[Production].[TransactionHistory].TransactionDate

 
And then the output of the “old” method :

            AncOp_PrjEl COL: Expr1003 

                ScaOp_Arithmetic x_aopAdd

                    ScaOp_Arithmetic x_aopAdd

                        ScaOp_Identifier COL: @uniqueDates 

                        ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=1) XVAR(varchar,Not Owned,Value=Len,Data = (1,;))

                    ScaOp_Convert varchar collate 872468488,Null,Var,Trim,ML=10

                        ScaOp_Identifier QCOL: [AdventureWorks2008R2].[Production].[TransactionHistory].TransactionDate

That is a little less compared to the COALESCE function. We see that the query with the COALESCE is also using this part, but also an IIF statement, and compare is not function and finally a convert. So with 1 + 1 = 2 i can come to the conclusion that when you want to use a COALESCE to create a separated list, that you are doing more then you should!

Happy coding!