User Defined Aggregates in SQL Server 2005 are one of the first new CLR objects that I will be talking about. UDAs as they are often abbreviated are commonly used to concatenate strings of data. For example, you can use them to create a comma seperate lis of E-mail addresses, etc.
As you have heard you can write .NET code and place it on a SQL Server to handle special functions. To do this, you make use of the new System.Data.SqlServer namespace.
To create a User Defined Aggregate, you must define four public methods: Init, Accumulate, Merge, and Terminate.
The Init method simply initializes the value you are aggregating to some value (i.e.: 0 for integers, a new string builder for strings, etc.)
public void Init()
{
returnString = new StringBuilder();
}
The Accumulate method is responsible for addiing new data to the existing data.
public void Accumulate(SqlString myString)
{
// append the new string with a comma
returnString.Append(myString + ", ");
}
The Merge method is used to accumulate items at the object level. In this case if this class was called Concatenate, the Merge method would look like this.
public void Merge(Concatenate myConcatenate)
{
returnString.Append(myConcatenate.returnString.ToString());
}
Lastly, the Terminate method does any special formatting before sending the aggregated value back. For example, this method could remove the command at the end of the string.
public SqlString Terminate()
{
return returnList.ToString();
}
If you notice there are some new datatypes (i.e.: SqlString, SqlDecimal). Basically these types match to SQL Server types. I'll cover these more in the future.
To use a UDA, an assembly has to be compiled and registered with SQL Server. Assuming the assembly was called MyAggregate.dll, you would register it like this:
CREATE ASSEMBLY MyAggregate
FROM 'MyAggregate.dll'
Note, you would have to specify the full path to the DLL on the SQL Server. Next you have to register the Aggregate in the assembly.
CREATE AGGREGATE Concatenate (@value varchar(max))
RETURNS varchar(max)
EXTERNAL NAME MyAggregate:Concatenate
To acctually use the aggregate in a query, you could do something like this:
SELECT dbo.Concatenate(EmailAddress) FROM blah
Today's tip has gone kind of lengthy, so if you are interested in the future, there is more information in the books we have.