Little SQL Server Tricks: The STRING_AGG() Function

When it comes to reporting, we often get some special requirements to transform data into a specific form. For one report we had a Workforce table with data that looks like this:

EmployeeId	FirstName	LastName	DepartmentId
1001	John	Smith	10
1002	Jane	Doe	10
1003	Max	Miller	20

For the report, we need to group the employees by DepartementId, and combine all employees with their EmployeeId into this form:

DepartmentId	EmployeeFormatted
10	John Smith (1001), Jane Doe (1002)
20	Max Miller (1003)

The STRING_AGG() function

For SQL Server 2017 and newer versions we can use the STRING_AGG function. This function is blazingly fast and has a syntax that is not too hard to understand:

SELECT DISTINCT
    DepartmentId, 
    STRING_AGG(CONCAT([FirstName], ' ', [LastName], ' (', [EmployeeId], ')'), ', ') 
        WITHIN GROUP (ORDER BY [EmployeeId]) AS EmployeeFormatted
FROM [Workforce]
GROUP BY DepartmentId;

The slow STUFF() function

If we need to do this report but have an older SQL Server, we can use the STUFF function. This function works, but it is much slower and harder to understand:

SELECT DISTINCT a.DepartmentId, 
(SELECT STUFF( (SELECT ' ' + s.[FirstName] + ' ' + s.[LastName] + ' (' + cast(s.[EmployeeId] as nvarchar) + '), '
FROM [Workforce] s
WHERE a.DepartmentId = s.DepartmentId
ORDER BY [EmployeeId]
FOR XML PATH('')
     ), 1, 1, '')) AS  EmployeeFormatted
FROM [Workforce] a

Conclusion

If you can, use the STRING_AGG() function over the STUFF() function. If you have a powerful machine and the right index on your table, you may get away with the STUFF() function for a long time. But in a more realistic scenario, for the same data the STRING_AGG() may take less than a second while STUFF() runs for more than 10 minutes. This is a massive difference, even for reporting that can be run in the background.