A SQL CLR user-defined aggregate - notes on creating and debugging

At the end of this post you will find my very first attempt to use one of the new features of SQL Server 2005 - CLR integration. It is a CLR user-defined aggregate that produces a comma-separated (comma plus space to be precise) list of values.

Column1
--------
aaa
bbb       -->   aaa, bbb, ccc
ccc

My code is a modified version of an example that I found here.

Global variables

You may expect (at least I did) that if you have a global variable (e.g. intermediateResult in my example) and you assign it a value in the Init method then you will be able to use this value in the Terminate method. Well, that's not true.

If you need to use a value of some variable in the Terminate method, you have to serialize it in the Write method (to save the needed value) and then deserialize it in the Read method and assign to some variable (to restore the needed value).

You can of course save and restore more than one variable - use some kind of serializable object to store the values.

Debugging

There is an MSDN article that explains exactly how to debug a CLR user-defined aggregate - Walkthrough: Debugging a SQL CLR User-Defined Aggregate - nonetheless, I encountered one problem.

I started with a solution that contained two projects - one project contained the aggregate code and the other was for a SQL test script. It is possible to perform debugging in such configuration (I managed to do it, although I am not sure how), but it is a better idea to have a single SQL Server project with both the aggregate code and SQL test scripts. This is what the IDE expects, so following this advice can spare you lots of hassle.

For example, MSDN says that to debug a CLR user-defined aggregate you need to enable CLR Debugging, but it doesn't say that Application Debugging must also be enabled.



When I worked with a single project, Visual Studio enabled this option automatically when needed, while with the two-project solution I had to do it myself.

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable()]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls=true,
    IsInvariantToDuplicates=false,
    IsInvariantToOrder=false,
    MaxByteSize=8000)]
public class Concat : IBinarySerialize
{
    #region Private fields
    private string separator;
    private StringBuilder intermediateResult;
    #endregion

    #region IBinarySerialize members
    public void Read(BinaryReader r)
    {
        this.intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
    #endregion

    #region Aggregation contract methods
    public void Init()
    {
        this.separator = ", ";
        this.intermediateResult = new StringBuilder();
    }

    public void Accumulate(SqlString pValue)
    {
        if (pValue.IsNull)
        {
            return;
        }

        if (this.intermediateResult.Length > 0)
        {
            this.intermediateResult.Append(this.separator);
        }
        this.intermediateResult.Append(pValue.Value);
    }

    public void Merge(Concat pOtherAggregate)
    {
        this.intermediateResult.Append(pOtherAggregate.intermediateResult);
    }

    public SqlString Terminate()
    {
        return this.intermediateResult.ToString();
    }
    #endregion
}