Project Description

This project contains a set of SQLCLR User-defined Aggregate functions (SQLCLR UDAs) that collectively offer similar functionality to the MySQL GROUP_CONCAT function. There are multiple functions to ensure the best performance based on the functionality required.

The SQLCLR objects in this project allow you to write code like this:

SELECT some_id,
       dbo.GROUP_CONCAT(some_column) AS as delimited_list
FROM dbo.some_table
GROUP BY some_id;

    to retrieve a resultset like this:

some_id           delimited_list
----------------- -----------------------------------
1                 red,green,blue
2                 cyan,magenta,yellow,key

MySQL offers an aggregate function that will concatenate grouped strings into a delimited list. There are many ways to accomplish the same result on SQL Server using various T-SQL methods, the most efficient of those native methods seems to be using XML functionality built into T-SQL, however none of the native T-SQL solutions offer as expressive a syntax as these SQLCLR UDAs. An extensive set of performance tests were carried out and the SQLCLR UDAs in this project are comparable in terms of performance to the T-SQL XML PATH and XML PATH, TYPE methods. In some scenarios the SQLCLR UDAs performed best (least CPU and elapsed time) and in other scenarios the T-SQL XML methods performed best. Overall performance is comparable between the SQLCLR UDAs and T-SQL XML methods. You can read more about the different options and their performance compared to these objects here.

In Microsoft SQL Server 2008 R2 Books Online the example presented for a SQLCLR UDA is an aggregate named Concatenate that offers similar functionality to MySQL's built-in GROUP_CONCAT function. The article resides here. The example aggregate function uses a StringBuilder to store the data internally. While the implementation is a proper example of a SQLCLR UDA it does not perform well over data sets that produce very long concatenations. The SQLCLR UDAs in this project implement a more efficient internal data structure than the StringBuilder used in the MSDN example.

This project contains the following aggregates:

• GROUP_CONCAT
• GROUP_CONCAT_D
• GROUP_CONCAT_DS
• GROUP_CONCAT_S

Visit the Documentation Page for a detailed description of each aggregate. 

Installation and Usage

To install the SQLCLR UDAs you will need only to execute one T-SQL script contained within the release downloadable attainable on the Downloads Page. Note that Visual Studio is not required to install or use the SQLCLR UDAs. See the Documentation Page for additional deployment and usage details.

Note that use of the SQLCLR UDAs implies you will enable the SQLCLR on your database instance.

For 32-bit instances where these functions will be used extensively I recommend you monitor memory usage closely and adjust your MemToLeave settings appropriately.

Support

If you would like to suggest a new feature or report a bug please add a new item using the Issue Tracker.