How to: Create and Run a SQL Server Aggregate by using Common Language Run-time Integration

Article
07/25/2011

Create SQL Server aggregates by adding Aggregate items to SQL Server Common Language Run-time (SQL CLR) database projects. After successful deployment, aggregates that are created in managed code are called and executed like any other SQL Server aggregate.

SQL Server aggregates require four specific methods be implemented; Init, Accumulate, Merge, and Terminate. For more information, see Requirements for CLR User-Defined Aggregates in the SQL Server Books Online on the Microsoft Web site.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Creating a SQL Server Aggregate

To create a SQL Server aggregate

Open an existing SQL CLR Database Project, or create a new one. For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.
On the Project menu, select Add New Item.
In the Add New Item dialog box, select Aggregate.
Type a Name for the new aggregate.
Add code to run when the aggregate is executed. See the first example that follows this procedure.
Deploy the aggregate to a SQL Server. For more information, see How to: Deploy SQL Server CLR Integration Database Project Items to a SQL Server.

Important

SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the 2.0, 3.0, or 3.5 version of the .NET Framework. If you try to deploy a SQL Server project to SQL Server 2005 or SQL Server 2008, an error appears: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (where AssemblyName is the name of the assembly that you are deploying). For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.
Debug the aggregate by executing it on the SQL Server. See the second example that follows this procedure.

Example

This example creates an aggregate to count vowels. This aggregate counts the vowels in a column of string data types. The aggregate contains the following four required methods, which can run multithreaded: Init, Accumulate, Merge, and Terminate.

Imports System
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()>
<SqlUserDefinedAggregate(Format.Native)>
Public Structure CountVowels

    ' count only the vowels in the passed-in strings
    Private countOfVowels As SqlInt32


    Public Sub Init()
        countOfVowels = 0
    End Sub


    Public Sub Accumulate(ByVal value As SqlString)
        Dim stringChar As String
        Dim indexChar As Int32

        ' for each character in the given parameter
        For indexChar = 0 To Len(value.ToString()) - 1

            stringChar = value.ToString().Substring(indexChar, 1)

            If stringChar.ToLower() Like "[aeiou]" Then

                ' it is a vowel, increment the count
                countOfVowels = countOfVowels + 1
            End If
        Next
    End Sub


    Public Sub Merge(ByVal value As CountVowels)

        Accumulate(value.Terminate())
    End Sub


    Public Function Terminate() As SqlString

        Return countOfVowels.ToString()
    End Function
End Structure

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.

Note

If you are using AdventureWorks2005, replace Person.Person with Person.Contact in the sample Transact-SQL code.

SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels
FROM Person.Person
GROUP BY LastName
ORDER BY LastName

How to: Create and Run a SQL Server Aggregate by using Common Language Run-time Integration

Creating a SQL Server Aggregate

To create a SQL Server aggregate

Example

See Also

Tasks

Reference

Concepts

Additional resources