Create and Run a CLR SQL Server Aggregate function in SQL-Server 2005 from Visual Studio 2008:

You might be aware with Aggregate function like MIN, MAX, SUM, COUNT etc. there are many types of Aggregate function supported by Microsoft SQL Server 2005 along with CLR Aggregate function. This is new and amazing feature which helps you create your own customize AGGREGATE function with the power of CLR and use it in SQL Server 2005. Isn’t it amazing? Yes, It is. Let us have a journey of creating CLR SQL Server Aggregate function to implement it in SQL Server 2005.

I have created COUNT aggregate function. It is actually inbuilt function but this is to give demo of creating aggregate function in SQL-Server 2005.

We will first create new project from Visual Studio 2008:

1) File->New->Project

2) From Visual C# tree on left panel select Database option

3) Select “SQL Server Project” from right side panel.

4) Give the name to your project. I gave “TestAggregate”

5) Click “OK”

6) Now from your solution explorer , right click on project name, select “Add->New ->Add Item”

7) Select “Aggregate” (as we are going to create aggregate function)

8) Give name to it. I gave “TestAGGR” then click “ADD” button.

After following above steps, you will see TestAGGR.cs file with some necessary namespace and methods.

Here is the brief description of those methods.

INIT(): will be executed once per aggregation

Accumulate(): will accumulate core logic and run per records.

Merge(): merge data from different method together.

Terminate(): finally returns the value.

Along with above methods, you can create methods as per your needs. Below is the copy of my TestAGGR.CS file. Have a look

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]

public struct TestAGGR

{

public void Init()

{

count = 0;

}

public void Accumulate(int Value)

{

count += 1;

}

public void Merge(TestAGGR value)

{

Accumulate(value.Terminate());

}

public int Terminate()

{

return count;

}

// This is a place-holder member field

private int count;

public int Getcount()

{

return count;

}

}

Once you done the above code in visual studio, build the project and copy the TestAggregate.dll from your BIN folder of your project and put it in “D”drive.

Now, we have done with Visual Studio and we will be moving to SQL-Server 2005 SSMS.

--enable your server for clr enable

--it is disable by default

sp_configure 'clr enable', 1

reconfigure

--create assembly of your DLL file

create assembly TestAggregate

authorization dbo

from 'D:\TestAggregate.dll'

with permission_set = safe

go

--create aggregate function of your DLL

CREATE AGGREGATE TestAggregate(@val Int)

RETURNS Int

EXTERNAL NAME TestAggregate.TestAGGR

go

--run your query and enjoy result

SELECT dbo.testaggregate(run) FROM PERSONALSCORE

I was Inspired to write this article by TECHNET and MSDN website of MICROSOFT.

http://technet.microsoft.com/en-us/library/ms131056.aspx#

http://msdn.microsoft.com/en-us/library/91e6taax(VS.80).aspx#

Reference: Ritesh Shah