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.
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 which seems to be using XML functionality built
into T-SQL, however none of the pure 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.
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.
To add a comment or ask a question about anything to do with the project please start a new discussion.