Creating a CLR user define aggregate (part 1). Simple CLR aggregate


SQL Server 2005 allows creating of User Defined Aggregate in any of the .NET  languages such as C# or VB. For simple cases like SUM or MAX you probably want  to use built-in aggregates, however there are cases  where build-ins are insufficient. In such cases people used to put the business logic on a client on a middle tier. With the new version of SQL Server you can have this logic on a server.


Let’s say company XYZ wants to come up with a way of calculating a bonus for their employees. XYZ uses NWIND database (NWIND database can be downloaded from http://msdn2.microsoft.com/en-us/library/ms143221.aspx). XYZ wants to have a business rule such that the bonus is never greater than 200% of the salary and each regular sale adds 1% to the bonus and each sale to Germany adds 3% to the bonus.


With the new Sql Server 2005 you can write your own aggregates in C# (or any .NET compatible language). Here is the aggregate.


[Serializable]


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


public struct Bonus


{


    private int m_nRegularSales;


    private int m_nGermanSales;


 


    public void Init()


    {


        m_nRegularSales = 0;


        m_nGermanSales = 0;


    }


 


    public void Accumulate(SqlString Country)


    {


        if (Country == “Germany”)


        {


            ++m_nGermanSales;


        }


        else


        {


            ++m_nRegularSales;


        }


    }


 


    public void Merge(Bonus Group)


    {


        m_nRegularSales += Group.m_nRegularSales;


        m_nGermanSales += Group.m_nGermanSales;


    }


 


    public int Terminate()


    {


        return Math.Min(200, (m_nRegularSales + 3 * m_nGermanSales));


    }


}


And here is a T-SQL query that uses this aggregate to calculate bonus for each employee.


select


      Employees.FirstName, Employees.LastName, dbo.Bonus(Orders.ShipCountry)


from


      Employees join Orders on Employees.EmployeeId = Orders.EmployeeId


group by


      Employees.EmployeeId, Employees.FirstName, Employees.LastName


 


[Posted by NikitaS]


Comments (7)

  1. mmkk says:

    i have a question?why use a struct,not a class?

  2. isaac says:

    Either is okay—it doesn’t really matter.  Struct is a little simpler because you have to set the layout on it.

  3. mmkk says:

    Thanks,i thought for some performance reason:)

    I’m sorry i have another question,when i create my own UDA,i meet a question:

    Msg 6558, Level 16, State 1, Line 1

    CREATE AGGREGATE failed because type ‘Bonus’ does not conform to UDAGG specification due to method ‘Accumulate’.

    Msg 6597, Level 16, State 2, Line 1

    CREATE AGGREGATE failed.

    I copy&paste your sample,but same error occurs.

  4. mmkk says:

    Finally i resolved this question,but i really don’t know why.

    I just used VS2005 to deploy the whole assembly,then delete the automatically generated UDA,and then i use my own t-sql script to create it manually,it works,but i didn’t change anything.That’s really confused me.

  5. Susantha says:

    This is excellent article spcecially those who new to CLR Integration.