CLR - Pro SQL Server Internals - page 267

Listing 13-17. String concatenation: T-SQL implementation

-- Using SQL Variable
declare
@V nvarchar(max) = N''

select @V = @V + convert(nvarchar(32), Num) + ','
from dbo.Numbers
where Num <= @MaxNum

-- removing trailing comma
select @V = case when @V = '' then '' else left(@V,len(@V) - 1) end

-- FOR XML PATH
select case when Result is null then '' else left(Result,len(Result) - 1) end
from
(
select
convert(nvarchar(max),
(
select Num as [text()], ',' as [text()]
from dbo.Numbers
where Num <= @MaxNum
for xml path('')
)
) as Result
) r

Table 13-6 shows the average execution time when we concatenate different numbers of rows.

Table 13-6. String concatenation: Execution time

CLR Aggregate SQL Variable FOR XML PATH

1,000 rows 3 ms 1 ms >1 ms

10,000 rows 12 ms 129 ms 3 ms

25,000 rows 33 ms 840 ms 6 ms

50,000 rows 63 ms 37,182 ms 21 ms

100,000 rows 146 ms 535,040 ms 43 ms

As you see, CLR aggregate has a slightly higher startup cost when compared to the T-SQL variable approach,
although that quickly disappears on larger rowsets. The performance of both the CLR aggregate and FOR XML PATH
methods linearly depend on the number of rows while the performance of the SQL Variable approach degrades
exponentially. SQL Server needs to initiate the new instance of the string every time it concatenates the new value,
and it does not work efficiently especially when string becomes large. Finally, the FOR XML PATH approach is the most
efficient regardless of the number of the rows concatenated.
The key point is that you always need to look at the options to replace imperative code with declarative set-based
logic. While CLR usually outperforms imperative T-SQL code, set-based logic outperforms both of them.