Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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

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.

NoteNote

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.

To create a SQL Server aggregate

  1. 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.

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

  3. In the Add New Item dialog box, select Aggregate.

  4. Type a Name for the new aggregate.

  5. Add code to run when the aggregate is executed. See the first example that follows this procedure.

  6. 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 noteImportant

    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.

  7. Debug the aggregate by executing it on the SQL Server. See the second example that follows this procedure.

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.


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.

NoteNote

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.