SQL Server 2005 introduced user-defined aggregate functions but restricted them to have one input parameter and to have a result that is 8000 bytes or less. SQL Server 2008 lifts those restrictions and allows multiple parameters and results that are up to two gigabytes in size. The restriction on the number of parameters can be sidesteped in SQL Server 2005 by turning multiple parameters into one parameter.
I had oringially intended to write up this tip and show how to pass a CLR User-Defined Type (UDT) to the aggregate as a way to pass the multiple arguments. UDT's are data types based on CLR types and they're described in the article SQL Server User-Defined Type To Store and Process Email Addresses. As I began to write the Parse method of the UDT, which is mandatory, I decided that the two paramaters would be seperated by a zero byte (0X00) or CHAR(0). The Parse method had to find the zero byte, split up the input string and use the results to populate the properties of the UDT. But if Parse method could do this, so could the Accumulate method of the aggregate. There wasn't a need for the UDT and the solution is:
Combine arguments by converting them to string and passing the result to the aggregate
Concatenate them with Char(0) as the seperator between parameters
Split the arguments in the Aggregate method of the aggregate
This approach eliminates the need to create the UDT and is thus much simpler. While the time to perform the string concatenation and parsing might be a little longer than the time to construct a CLR type and populate it, the difference can't be much and due to the size limitation on the aggregate of 8000 bytes it's unlikely to be noticed.
Using Visual Studio 2005 and SQL Server 2005 a C# SQL Project contains the aggregate. The process of creating a SQL CLR project with Visual Studio has been described in earlier articles such as Writing to an operating system file using the SQL Server SQLCLR. The C# SQL Server Project is in the Project Type "Visual C#\Database" in the New Project dialog. Start by adding an aggregate. I named mine concat5 using the menu command "Project\Add Aggregate...". The code is very similar to the code found in How to pass multiple arguments to a CLR aggregate in SQL Server 2008 the differences is that the Aggregate method has the additional job of splitting the string into it's parts. Here's the code:
Use concat5 the same way the SQL Server 2008 version is used to aggregate a column. This example uses the AdventureWorks database and produces a short list of provinces delimited by pipe characters:
, dbo.concat5(StateProvinceName + CHAR(0) + '|')
WHERE TerritoryName IS NOT NULL
GROUP BY TerritoryName
United Kingdom England
To make using concat5 a bit easier comma is used as a default seperator. That allows the Char(0) and deliminator to be omitted and concat5 works like the Concatenate aggregate from the Microsoft samples. It's still possible to include the Char(0) but no deliminator to have the strings joined without a deliminator.
With the original SQL Server 2005 version of this function, which was called Concatenate, we had to worry about creating aggregates that went over 8000 bytes. That's is not a problem with the concat aggregate created for SQL Server 2008, but working in SQL Server 2005 it becomes a problem again. Concatenating more than 8000 bytes causes SQL Server to throw an error as the size of the aggregate grows as seen here:
SELECT DATALENGTH(dbo.concat5(Name + CHAR(0) + ';')) [Concat Len] FROM AdventureWorks.Sales.Store GO
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "concat5":
System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.
at System.Data.SqlTypes.SqlBytes.Write(Int64 offset, Byte buffer, Int32 offsetInBuffer, Int32 count)
at System.Data.SqlTypes.StreamOnSqlBytes.Write(Byte buffer, Int32 offset, Int32 count)
at System.IO.BinaryWriter.Write(String value)
at concat5.Microsoft.SqlServer.Server.IBinarySerialize.Write(BinaryWriter w)
Build the concat5 aggregate for your SQL Server 2005 databases
Create aggregates with multiple arguments in SQL Server 2005 by combining them into a single string argument seperated with zero bytes.
Last Update: 6/21/2010
About the author
Andy is a SQL Server Developer in the Boston area with 25 years of database and application development experience.
If you use a User defined datatype you can use the datatype to hold 2 values.
If you create also a function to create the datatype based on the 2 values you don't need any string manipulation.. it will be much faster.
Also.. you could use a other datatype if you want your aggregate to return multiple values, I use this for certain 'special' aggregates and the possibilities are endless..
Thursday, May 23, 2013 - 12:17:44 PM - Federico Caselli