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.
 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. |
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.
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.
|
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
// list of vowels to look for
private const string vowels = "AEIOUaeiou";
public void Accumulate(SqlString value)
{
if (!value.IsNull)
{
string theString = value.ToString();
int stringLength = theString.Length;
// for each character in the given parameter
for (int i = 0; i < stringLength; 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 (theString.Substring(i, 1) == vowels.Substring(j, 1))
{
// it is a vowel, increment the count
countOfVowels += 1;
}
}
}
}
}
- 2/2/2012
- Always_Learning
- 2/2/2012
- Always_Learning
- 6/6/2011
- Jaco de Groot
- 6/6/2011
- Jaco de Groot
