Creating a custom SQL aggregate function in C#

The fact that sql2005 can host the CLR was a big selling point. I kind of always knew that it was possible to extend and create new SQL functions in C# but never really took the time to do it myself.

What do we want to do?

I’d like to be able to create a function that works like the .NET String.Join static method. Here is a sample query that would demonstrate the use our new aggregate function:

select PizzaRecipeId, dbo.strjoin(IngredientName)
from PizzaRecipe
group by PizzaRecipeId

This query would output something like:

1 Cheese, Chicken, BBQ Sauce
2 Cheese, Ham
3 Cheese, Chicken, Pineapple

Let’s do it :)

The first thing is to write the c# code.
Create a new class library project and add a new class called Concatenate. Our new class must:
- have a serializable attribute
- have the SqlUserDefinedAggregate attribute
- implement IBinarySerialize
- have the following 4 methods (there is no interface for them)
o public void Init()
o public void Accumulate(SqlString value)
o public void Merge(Concatenate other)
o public SqlString Terminate()

The implementation itself is straightforward, using a string builder to store the data:

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=8000)]
public class Concatenate : IBinarySerialize
{
private StringBuilder sb;

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

public void Accumulate(SqlString value)
{
if (sb.Length != 0)
{
sb.Append(", ");
}

sb.Append(value);
}

public void Merge(Concatenate other)
{
sb.Append(other.sb);
}

public SqlString Terminate()
{
return new SqlString(sb.ToString());
}

#region IBinarySerialize Members

public void Read(BinaryReader r)
{
sb = new StringBuilder(r.ReadString());
}

public void Write(BinaryWriter w)
{
w.Write(sb.ToString());
}

#endregion
}

If the project compiles, let’s continue with the commands that will actually load the dll into sql server and reference the aggregate function:

create assembly clr_integration from 'C:\dev\learning\sql2005\ \clr_integration\bin\debug\clr_integration.dll' with permission_set = safe
GO

create aggregate strjoin(@input nvarchar(200)) returns nvarchar(max)
external name clr_integration.Concatenate

The permission_set is safe because the assembly does not perform any interop or contains any unsafe blocks.

Let’s create a table and some dummy data:


if (object_id('PizzaRecipe') is not null) drop table PizzaRecipe
create table PizzaRecipe
(
PizzaRecipeId int NOT NULL,
IngredientName nvarchar(200)
)

insert PizzaRecipe values(1, 'Cheese')
insert PizzaRecipe values(1, 'Chicken')
insert PizzaRecipe values(1, 'BBQ Sauce')
insert PizzaRecipe values(2, 'Cheese')
insert PizzaRecipe values(2, 'Ham')
insert PizzaRecipe values(3, 'Cheese')
insert PizzaRecipe values(3, 'Chicken')
insert PizzaRecipe values(3, 'Pinnaple')

Everything should now be ready and it’s time to test it :)

Writing the aggregate function was actually simpler than what I imagined. It’s now just a matter of detecting when this type of feature can be used.