In this post we will learn how to implement UDF CLR Aggregates ,
In order to implement CLR UDF aggegate assembly we need to follow set of rules or requirements in technical terms, if any of rules are broken then it is not a valid
CLR UDF
- Like any other CLR assembles in SQL Server, a CLR UDF Aggregate should have a attribute called SqlUserDefinedAggregat
In order to save the Intermediate state of Aggregation we use Four methods in the Aggregation- Init
- Accumulate
- Merge
- Terminate
Init :
Syntax : public void Init();
Once this method is invoked the Query processor Initializes the aggregation , for every aggregation we need to invoke this method.
Accumulate:
Syntax: public void Accumulate ( input-type value[, input-type value, …]);
Input type is any managed sql server data types
This where actual data aggregation occurs , it may be summing or multiplying , counting.
Merge:
This method can be used to merge another instance of this aggregate class with the current instance. The query processor uses this method to merge multiple partial computations of an aggregation.
Terminate:
public return_type Terminate();
This method completes the aggregate computation and returns the result of the aggregation. The return_type should be a managed SQL Server data type.
The basic aggregate also has four attributes which are described in Andy Novak’s article.
| Attribute | Comment |
|---|---|
| Format | This is a compulsory enum with options Native, Unknown and UserDefined. |
| IsInvariantToNulls | Setting this to true tells the aggregate that it doesn’t care whether or not values are null or not, it will always produce the same result. An example of an aggregate where the value for this is false is COUNT(field) which only counts instances that are NOT NULL |
| IsInvariantToDuplicates | Setting this to true tells the aggregate that it will produce the same results even if there are duplicate entries. MAX() is a good example of this as no matter how many duplicates there are the MAX value will still be the MAX value. |
| IsInvariantToOrder | Setting this to true affects how SQL Server calls the Merge method of the aggregate. If you had a string concatenation aggregate that was parallelised then it is vitally important that the streams merge back together in the correct order. |
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct CountVowels
{
// count only the vowels in the passed-in strings
private SqlInt32 countOfVowels;
public void Init()
{
countOfVowels = 0;
}
public void Accumulate(SqlString value)
{
// list of vowels to look for
string vowels = "aeiou";
// for each character in the given parameter
for (int i=0; i < value.ToString().Length; i++)
{
// for each character in the vowels string
for (int j=0; j < vowels.Length; j++)
{
// convert parameter character to lowercase and compare to vowel
if (value.Value.Substring(i,1).ToLower() == vowels.Substring(j,1))
{
// it is a vowel, increment the count
countOfVowels+=1;
}
}
}
}
public void Merge(CountVowels value)
{
Accumulate(value.Terminate());
}
public SqlString Terminate()
{
return countOfVowels.ToString();
}
}
After deploying the aggregate, test it by executing it on the SQL Server and verifying the correct data is returned.
This query returns a result set of the vowel count for all the values in the LastNames column in the Contact table.
SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels FROM Person.Contact GROUP BY LastName ORDER BY LastName
Sqljunkieshare 