Custom SQL Server 2005 Aggregates

One of a series of posts from andraszek.net posted originally between 2006 and 2010. 

Custom aggregates are as fast as built in T-SQL aggregates like MAX(), SUM(), etc..
Here is the C# source code for an aggregate that concatenates short strings:

using System;
using System.Text;
using System.IO;

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(
	Format.UserDefined, //use clr serialization to serialize the intermediate result
	IsInvariantToNulls = true, //optimizer property
	IsInvariantToDuplicates = false, //optimizer property
	IsInvariantToOrder = false, //optimizer property
	MaxByteSize = 8000) //maximum size in bytes of persisted value
]

public struct Concatenate : IBinarySerialize
{
  private StringBuilder _IntermediateResult;
  // 8000 - 2 control bytes - 4 bytes for 2 UTF-16 characters = 7994
  private const int _MaxSize = 7994; 

  public void Init()
  {
    _IntermediateResult = new StringBuilder();
  }

  public void Accumulate(SqlString value)
  {

    if (!value.IsNull 
      && (_IntermediateResult.Length + value.GetUnicodeBytes().Length 
          < _MaxSize))
        {
          _IntermediateResult.Append(value.Value).Append(", ");
        }
  }

  public void Merge(Concatenate group)
  {
    if ((_IntermediateResult.Length + group._IntermediateResult.Length) 
      < _MaxSize)
    {
      _IntermediateResult.Append(group._IntermediateResult);
    }
  }

  public SqlString Terminate()
  {
    string output = String.Empty;
    // Delete the trailing comma and space, if any
    if (_IntermediateResult != null && _IntermediateResult.Length > 1)
    {
      output = _IntermediateResult.ToString(0, _IntermediateResult.Length - 2);
    }
    return new SqlString(output);
  }

  public void Read(BinaryReader reader)
  {
    _IntermediateResult = new StringBuilder(reader.ReadString());
  }

  public void Write(BinaryWriter writer)
  {
    writer.Write(_IntermediateResult.ToString());
  }
}

And here it is in action:

CREATE AGGREGATE Concatenate (@input nvarchar(4000)) RETURNS nvarchar(max)
	EXTERNAL NAME SqlClr.Concatenate
GO

SELECT Title, dbo.Concatenate(FirstName) AS [First Names] 
	FROM Person.Contact 
	GROUP BY Title


Title    First Names
-------- ---------------------------------------------
Sr.      José, Jésus, Anibal, José, Luis, Gustavo, Ciro, Humberto, Alvaro, Adrian, Ramón
Sra.     Janeth, Pilar, Janaina Barreiro Gambaro
[...]