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

Tuesday, March 10, 2009 |

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.


Reference: Ritesh Shah

0 comments: