Q: Why should I bother using these UDAs when I can get the same results using T-SQL w/ XML and avoid enabling the SQLCLR?
A: It is true that you can arrive at the same result using T-SQL w/ XML and avoid enabling the SQLCLR. If you are in an environment where SQLCLR is disabled, and the powers-that-be will not allow it to be enabled for one reason or another, then unfortunately this solution will not work for you. However, if SQLCLR is an option, the UDAs offer a more intuitive and concise syntax than do the XML methods and they provide comparable or better performance* than the XML methods.
* depends on use case, see next FAQ
Q: How do the UDAs stack up with other methods in terms of performance?
A: An extensive performance analysis was done where results of the "XML PATH" *, "XML PATH,TYPE" and UDAs were compared when handling 6 different use cases. When the "XML PATH" method was included in the testing (it is not an option for volatile data because of the escape issue) the UDAs outperformed the XML methods in 3 of 6 use cases. When the "XML PATH" method was excluded due to the character escape issue the UDAs outperformed the "XML PATH,TYPE" method in 4 of 6 use cases.
* the "XML PATH" method suffers from an issue where ampersands and angle brackets are escaped as & < and > which disqualifies them from use in many scenarios due to the display problems that can cause
Q: Why provide four UDAs instead of just one with all the parameters one might need?
A: SQL CLR functions do not currently allow for overloading as in .NET methods. For each specialized interface a separate UDA is required. Performance tip: Pick the correct UDA for your use-case. If you want a comma-delimited unsorted list there is no need to use GROUP_CONCAT_D or GROUP_CONCAT_DS because comma is the default delimiter and they will perform slightly worse over the same data than GROUP_CONCAT and GROUP_CONCAT_S respectively.
Q: Why not use the example UDA from Microsoft Books Online?
A: The UDAs in this project utilize a more efficient internal data structure for storing, joining and sorting grouped strings.
The Main directory contains the source code in the downloadable release bundle. If you are interested only in the code supporting the released solution look in the Main directory.
The Experimental directory contains source code from my research & development that lead to the code contained in the Main directory. However, the code in Main is not related to Experimental by any branching. It was a pre-cursor to the Main code and is there for your consideration, to show other methods reviewed and attempted before the the technique emerged and Main was developed.