This is next write-up in the multipart series for SQL Server Integration with CLR aka SQL CLR and discusses the creation of SQL server aggregate in SQL CLR.
Introduction and background
In the first article, we discussed what is SQLCLR, why SQLCLR and the steps for employing SQL CLR into our work. The next article, we explored how to create a stored procedure. The following article, we learnt to create a UDF and in this article, we would see the creating a SQL server aggregate in SQL CLR.
Aggregate is a term which is relatively unknown but as the term suggests it is essentially a function to perform some calculation on set of value and eventually return a single value. The common examples are AVG, MAX, MIN, COUNT etc. These functions are generally used in GROUP BY clause.
Objective
To learn how to create a SQL server aggregate in SQL CLR and use it.
Description
Note: The tool for development is VS 2010 and SQL server 2008 R2. The database “AdventureWorks” is referred. The first step in learning the SQL CLR database items is to create a SQL CLR project. 
When an aggregate is added, the Visual Studio adds a structure in the file as shown below which is unlike the stored procedure or UDF where we have a class and a static function to represent the stored procedure and UDF.
See the following image to understand about the type “struct” and its corresponding template.
We would have an aggregate for counting the specific character in string. Let’s implement for counting character “C” and that is case insensitive.
Complete code snippet is as follows.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct CharacterCounter
{
private SqlInt32 countOfChar;
public void Init()
{
countOfChar = 0;
}
public void Accumulate(SqlString Value)
{
string character = "C";
for (int i = 0; i < Value.ToString().Length; i++)
{
// convert parameter character to lowercase and compare to chracter also converted to lowercase
if (Value.Value.Substring(i, 1).ToLower() == character.ToString().ToLower())
{ // it is a character, increment the count
countOfChar += 1;
}
}
}
public void Merge(CharacterCounter Value)
{
Accumulate(Value.Terminate());
}
public SqlString Terminate()
{
return countOfChar.ToString();
}
}
The build and deployment (shortcut key Ctrl + F5) process is same in Visual Studio as it is for other database items like stored procedure and UDF etc.
Through TSQL the aggregate can be created as follows. (This step is to be performed after the registration of assembly).
CREATE AGGREGATE [dbo].[CharacterCounter]
(@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME [SqlServerProject].[CharacterCounter]
GO
Yeah, we are created our first aggregate and deployed too. This is the time to test. Following T SQL needs to be put in “Test.sql” for testing through Visual studio or we can test this T SQL on SQL server management studio.
SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CharacterCounter(LastName) ASChatacterCounter
FROM Person.Contact
GROUP BY LastName
ORDER BY LastName
The results of testing in management studio are depicted in following image.
The results can be seen in “Database Output” as below.
Creating, building, deploying and running (testing) a SQLCLR aggregate is also straightforward and with VS 2010 support for build and deploy and also provision for test output in one go, is great booster. These tools have taken care of manual tasks and giving the developer’s time for doing what they should be doing.
Summary and Conclusion
We are through our first exercise of creating a aggregate in SQL CLR along-with deployment and testing. We have seen the deployment through Visual Studio as well as T SQL deployment. It is an exciting amalgamation of programming world with database world and it looks seamless.
Hope this helps to help understand how to create SQL CLR aggregate and start exploring the SQL CLR world.
HAPPY PROGRAMMING!!!