Create user defined type, function and aggregate in SQL-Server 2005

Sometimes, we want to create a aggregate function used like AVG, SUM and Count bulit in functions with "group by" in SqlSever. It is used like this:

 

select AccountID, dbo.func_AverageLastMonths(dbo.func_MakeParameters(t.yearMonth, absolutereturnpctnet, 2010,5,3)) as Rate3Months
from AccountMonthFact as am
join Time as t
on am.TimeNodeID = t.TimeNodeID and am.absolutereturnpctnet is not null
group by AccountID;

 

func_AverageLastMonths is an user defined aggragate. How to create it?

The procedure has the following steps:

<1> First,we need to run the following reconfiguration command the first time you use CLR integration in a SQL Server instance.

sp_configure 'clr enabled', 1
GO
RECONFIGURE

<2> Next, we need to create the source code using C# or VB (any language supported by NET).

A aggregate is a class including four basic methods, like this:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct CountNulls
{
  public void Init()
  {
　　// Put your code here
  }

  public void Accumulate(SqlString Value)
  {
　　// Put your code here
  }

  public void Merge(CountNulls Group)
  {
　　// Put your code here
  }

  public SqlString Terminate()
  {
　　// Put your code here
　　return new SqlString("");
  }
　　// This is a place-holder member field
　private int var1;
}

Unfortunately, in Sqlserver 2005, Accumulate method only accept one paramter, therefore, if we want to pass more than one parameters, we need to creat our own new type to wrap all parameters as one passed to Accumulate method, and create a new function to generate instance of this new type. Hence now talk about how to create user defined sql type and function.

A. How to create new sql type?

　　1. Write the .NET code for the assembly.

　　2. Compile the .NET code.

　　3. Create and register the assembly as a database object within SQL Server.

　　4. Create the UDT based on your assembly

Here is a example used in the example showed at the begining of the article.

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct ParameterData : INullable
{
    public override string ToString()
    {
        throw new Exception("The method or operation is not implemented.");
    }

    public bool IsNull
    {
        get
        {
            return false;
        }
    }

    public static ParameterData Null
    {
        get
        {
            throw new Exception("The method or operation is not implemented.");
        }
    }
    

    public static ParameterData Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        ParameterData p = new ParameterData();
        // Put your code here
        return p;
    }
 

    public int yearMonth;
    public double rate;
    public int currentYear;
    public int currentMonth;
    public int period;
}

It is important to describe the Format attribute which is related to serialization. If the class only contains primitive type variable, Format.Native is ok because NET will automatically serialize all variables. But if the class contains reference types, like String, List, Format.UserDefined must be used because we need to serialize all variables(including other primitive types) by ourselves.

B. How to create user defined function?

Here is an example.

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static ParameterData MakeOrder(int yearMonth, double rate, int currentYear, int currentMonth, int period)
    {
        ParameterData p = new ParameterData();
        p.yearMonth = yearMonth;
        p.rate = rate;
        p.currentYear = currentYear;
        p.currentMonth = currentMonth;
        p.period = period;

        return p;
    }
};

<3> After creating source code, next is to create assembly.

create assembly AverageLastMonths
from 'C:\Jia_Function\AverageLastMonths.dll'
with permission_set=safe

<4> Use assembly to create new type, function and aggrgate.

create type ParameterData
external name AverageLastMonths.[ParameterData];

create Aggregate func_AverageLastMonths(@p ParameterData)
returns nvarchar(400)
external name [AverageLastMonths].[LastMonthRate];

create function func_MakeParameters
( — 53 ,referes to mapping
@yearMonth nvarchar(20), @rate float, @currentYear nvarchar(20), @currentMonth nvarchar(20), @period int
) 
returns ParameterData
as
external name [AverageLastMonths].UserDefinedFunctions.MakeOrder;

Until now, we have implemented the whole aggregate. It can be used like this.

select AccountID, dbo.func_AverageLastMonths(dbo.func_MakeParameters(t.yearMonth, absolutereturnpctnet, 2010,5,3)) as Rate3Months
from AccountMonthFact as am
join Time as t
on am.TimeNodeID = t.TimeNodeID and am.absolutereturnpctnet is not null
group by AccountID;

---------------------------------------------------------------------------------------------------------------------------------------

Here is the whole source code.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Runtime.InteropServices;

/**
** FAP-3418 Create SQL Server Function 'func_ AverageLastMonths' for 'star schema' sample queries
** The functions is used to calculate metrics like 'Rate1Month','Rate3Month','Rate1Year','Rate3Year','Rate5Year', 
** which can be found in all levels of calculation, such as Account level, FA level.etc
*/


[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct ParameterData : INullable
{
    public override string ToString()
    {
        throw new Exception("The method or operation is not implemented.");
    }

    public bool IsNull
    {
        get
        {
            return false;
        }
    }

    public static ParameterData Null
    {
        get
        {
            throw new Exception("The method or operation is not implemented.");
        }
    }
    

    public static ParameterData Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        ParameterData p = new ParameterData();
        // Put your code here
        return p;
    }
 

    public int yearMonth;
    public double rate;
    public int currentYear;
    public int currentMonth;
    public int period;
}

/////////////////////////////////////////////////////////////////////


public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static ParameterData MakeOrder(int yearMonth, double rate, int currentYear, int currentMonth, int period)
    {
        ParameterData p = new ParameterData();
        p.yearMonth = yearMonth;
        p.rate = rate;
        p.currentYear = currentYear;
        p.currentMonth = currentMonth;
        p.period = period;

        return p;
    }
};


///////////////////////////////////////////////////////////////////////////////////////////////////
// Cautions: plz remeber to serialize all variables in aggregate, if you choose UserDefined format.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
[StructLayout(LayoutKind.Sequential)]
public struct LastMonthRate: IBinarySerialize
{
    private List<double> rateInEachMonth;
    private int period;
    
    public void Write(System.IO.BinaryWriter w)
    {
        // Write out how many
        w.Write(period);
        w.Write(rateInEachMonth.Count);
        // Write out each element
        foreach (double rate in rateInEachMonth)
        {
            w.Write(rate);
        }
    }
    
    public void Read(System.IO.BinaryReader r)
    {
       rateInEachMonth = new List<double>();
       period = r.ReadInt32();
       Int32 ii = r.ReadInt32();
       
       for (int i = 1; i <= ii; i++)
       {
           double rate = r.ReadDouble();

           rateInEachMonth.Add(rate);
       }
    }

 
   public void Init()
    {
        rateInEachMonth = new List<double>();
        period = 0;
    }
 
   public void Accumulate(ParameterData p)
    {
       if(p.IsNull) return;
       
       this.period = p.period;
       
       /// int currentYearMonth = (p.currentYear - 2000) * 100 + p.currentMonth;
       int thisYear = p.yearMonth/100 + 2000;
       int thisMonth = p.yearMonth%100;
       
       if (((p.currentYear-thisYear)*12 + p.currentMonth-thisMonth+1)<= p.period)
           rateInEachMonth.Add(p.rate);
       
       
    }
 
   public void Merge(LastMonthRate Group)
    {
        this.rateInEachMonth.AddRange(Group.rateInEachMonth);
    }

   public SqlDouble Terminate()
    {
        if(rateInEachMonth.Count != this.period) 
            return SqlDouble.Null;
        
        double res = 1.0;  /// != period case, can get result
        foreach (double rate in rateInEachMonth)
        {
            res *= (rate/100+1);
        }

        res -= 1;
        res *= 100;

        if (period > 12)
        {
            double t = res / 100 + 1;

            int sig = Math.Sign(t);

            double pow = Math.Pow(Math.Abs(t), (float)1 / (period / 12));

            res = sig * pow;

            res -= 1;

            res *= 100;
        }
        
        
        
        return (SqlDouble)(res);
    }

   
    
    
    
}