SQL Inline or Scalar Function?

Question

Hi,

So I need an SQL function that will concatenate a bunch of row values into one varchar.

I have the functions written but right now I'm focused on what is the better choice for performance.

The Scalar Function is

CREATE FUNCTION fn_GetPatients_ByRecipient (@recipient int)
RETURNS varchar(max) 
AS
BEGIN
DECLARE @patients varchar(max)
SET @patients = ''
SELECT @patients = @patients + convert(varchar, Patient) + ';' FROM RecipientsPatients WHERE Recipient = @recipient
RETURN @patients
END

The Inline Function just returns a table of all the values instead of concatenating them.

CREATE FUNCTION fn_GetPatients_ByRecipient (@recipient int)
RETURNS TABLE 
AS
RETURN
(
    SELECT Patient FROM RecipientsPatients WHERE Recipient = @recipient
)

I would then take this table in a separate function and concatenate them together. I was thinking the second choice is best since I will be going row by row through a smaller data set. Any opinions on what I'm doing right/wrong would be appreciated.

Thanks

Answer 1

Have a look at Adam Machanic's results from his Grouped String Concatenation Contest:

http://sqlblog.com/blogs/adam_machanic/archive/2009/05/31/grouped-string-concatenation-the-winner-is.aspx

It has the code to show you the most efficient way to do this. Peter Larsson, who won the contest, used a combination of tricks including XML PATH to accomplish the task. There was some debate later about whether it was the most efficient solution based on subsequent tests of other submissions. Make sure you check the comments to know what scripts to look at in the zip file you can download there. Generally FOR XML PATH('') is the fastest though.

Answer 2

This problem of string concatenation in SQL Server has several solutions, and the pros and cons are discussed in Concatenating Row Values in Transact-SQL and other similar articles on the web.

My favourite solution is using the FOR XML PATH(' ') trick. The chain assignment method you use works fine, although is not officialy supported and hence may break in future. Your method should be among the fastest possible, if not the fastes, as long as the table valued function does not perform a full scan, ie. you have an index on Recipient that covers Patient (use include).

The only thing I would add is to declare both functions WITH SCHEMABINDING, this has side effects that improve performance.

Answer 3

See here for an example of using the FOR XML PATH trick

set nocount on;
declare @t table (id int, name varchar(20), x char(1))
insert into @t (id, name, x)
select 1,'test1', 'a' union
select 1,'test1', 'b' union
select 1,'test1', 'c' union
select 2,'test2', 'a' union
select 2,'test2', 'c' union
select 3,'test3', 'b' union
select 3,'test3', 'c' 


SELECT p1.id, p1.name,
          stuff((SELECT ', ' + x
              FROM @t p2
             WHERE p2.id  = p1.id
             ORDER BY name, x
               FOR XML PATH('') ), 1,2, '') AS p3
      FROM @t p1
     GROUP BY 
     id, name

it returns

1   test1 a, b, c
2   test2 a, c
3   test3 b, c