Export (0) Print
Expand All

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.

NoteNote

The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server and must be enabled in order to use SQL Server project items. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure. From more information, see Enabling CLR Integration.

NoteNote

SQL Server aggregates require four specific methods be implemented; Init, Accumulate, Merge, and Terminate. For more information, see the SQL CLR .NET User-Defined Aggregate Functions topic in the SQL Books Online.

NoteNote

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

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.

NoteNote

C++ examples must be compiled with the /clr:safe compiler option.

  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.

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

See Also

Community Additions

ADD
Show:
© 2014 Microsoft