How to: Create and Run a CLR SQL Server Aggregate

Create SQL aggregates by adding Aggregate items to SQL Server projects. After successful deployment, aggregates created in managed code are called and executed like any other SQL Server aggregate.

Creating a SQL Server Aggregate

To create a SQL Server aggregate

1. Open an existing SQL Server Project, or create a new one. For more information, see How to: Create a SQL Server Project.

2. From the Project menu, select Add New Item.

3. Select Aggregate in the Add New Item Dialog Box.

4. Type a Name for the new aggregate.

5. Add code to run when the aggregate is executed. See the first example below.

1. Deploy the aggregate to a SQL Server. For more information, see How to: Deploy SQL Server Project Items to a SQL Server.

2. Debug the aggregate by executing it on the SQL Server. See the second example below.

Example

This example creates an aggregate to count vowels. This aggregate counts the vowels in a column of string datatypes. 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();
    }
}

#include "stdafx.h"

#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlTypes;
using namespace Microsoft::SqlServer::Server;

// In order to debug your Aggregate, add the following to your debug.sql file: 
// 
// SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels 
// FROM Person.Contact 
// GROUP BY LastName 
// ORDER BY LastName 
//

[Serializable]
[SqlUserDefinedAggregate(Format::Native)]
public value struct CountVowels
{
public:
    void Init()
    {
        countOfVowels = 0;
    }

    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;
                    break;
                }
            }
        }
    }

    void Merge(CountVowels value)
    {
        Accumulate(value.Terminate());
    }

    SqlTypes::SqlString Terminate()
    {
        return countOfVowels.ToString();
    }

private:
    // count only the vowels in the passed-in strings
    SqlInt32 countOfVowels;
};

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

See Also

Tasks

How to: Create a SQL Server Project

How to: Create and Run a CLR SQL Server Stored Procedure

How to: Create and Run a CLR SQL Server Trigger

How to: Create and Run a CLR SQL Server Aggregate

How to: Create and Run a CLR SQL Server User-Defined Function

How to: Create and Run a CLR SQL Server User-Defined Type

Walkthrough: Creating a Stored Procedure in Managed Code

How to: Debug a SQL CLR Stored Procedure

Concepts

Introduction to SQL Server CLR Integration (ADO.NET)

Advantages of Using Managed Code to Create Database Objects

Item Templates for SQL Server Projects

Reference

Attributes for SQL Server Projects and Database Objects

Other Resources

SQL CLR Database Debugging