Creating a SQL Server CLR annualised product function

Introduction

There's nothing particularly original about creating a PRODUCT function in SQL Server using the CLR. Follow this link to see how to implement one. Recently, however, I had the need to return an annualised product of a set of values. For example, consider the following table of quarterly percentage return figures: –

The percentage return over the period as a whole would be: –

(((1.055 * 1.07 * 1.031 * 1.018 * 1.01 * 1.022 * 1.008 * 1.065) – 1) * 100) = 31.29 %

and to calculate the annual return we need to raise this result to the power of ½ (if we had three years of data then we'd use ⅓).

31.29 % ^ 0.5 = 14.58 %

Now the T-SQL to do this isn't particularly difficult, but it can get pretty untidy, so what follows is a CLR implementation written in C#.

Aggregate functions are defined as structs. The struct should be marked as serializable so that SQL Server 2005 can store a representation of the aggregate on disk and the SqlUserDefinedAggregate attribute indicates that this is a user-defined aggregate. The Format.Native property tells the Framework to handle the serialization process using a simple algorithm and it works only with value types which is why the aggregate is defined as a struct. Within our struct, we have defined four private variables: –

• _product: holds the running total of the product of all the values currently aggregated.
• _annualisedProduct: takes the value of the _product variable and, if the _count of values is greater than the defined _num value, raises the value to the power of the _num divided by the _count.
• _num: is the number of periods in one year. For this function (quarterly) the value is 4, to implement a monthly variation the value would be 12.
• _count: stores the running count of the values in the set.

Within the struct, four methods need to be implemented: –

Init()

The Init() method initializes any variables or resources required and is called once when the aggregate function is invoked. Our Init() method set the _product variable to 1, _num to 4 and _count to 0.

Accumulate()

The Accumulate() method contains the implementation of the aggregate function and is called once for each row. Our implementation increments the _count variable, calculates the current _product and then determines the _annualisedProduct value.

Merge()

If the column to aggregate contains a large number of rows, SQL Server may split the rows into groups, aggregate each group and then merge the results of each group.

Terminate()

The Terminate() function is called to free any resources used and returns the result of the function, the _annualisedProduct.

Below is the final code for the struct: –

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 300, IsNullIfEmpty = true)]
public struct AnnualisedProductQuarterlyData : IBinarySerialize
{ 
 
    private Decimal _product;
    private Decimal _annualisedProduct;
    private Int32 _num;
    private Int32 _count;
 
    public void Init()
    {
        _product = 1;
        _num = 4;
        _count = 0;
    }
 
    public void Accumulate(SqlDecimal value)
    {
        _count++;
        _product = _product * (Decimal)value;
        if (_count > _num)
        {
            _annualisedProduct = (Decimal)Math.Pow((Double)_product, (Double)(_num / _count));
        }
        else
        {
            _annualisedProduct = _product;
        }
    }
 
    public void Merge(AnnualisedProductQuarterlyData Group)
    {
        Accumulate(Group.Terminate());
    }
 
    public SqlDecimal Terminate()
    {
        return _annualisedProduct;
    }
 
    #region IBinarySerialize Members
     
        public void Read(System.IO.BinaryReader r)
        {
            _annualisedProduct = r.ReadDecimal();
        }
     
        public void Write(System.IO.BinaryWriter w)
        {
            w.Write(_annualisedProduct);
        }
     
    #endregion
 
}

The T-SQL to register the assembly and functions in your database is as follows: –

CREATE ASSEMBLY [StuartWhiteford] 
FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\\MSSQL\Binn\StuartWhiteford.SQLServer.dll'
WITH PERMISSION_SET = SAFE
 
CREATE AGGREGATE [dbo].[Product] (@input decimal(38,10))
RETURNS decimal(38,10)
EXTERNAL NAME [StuartWhiteford].[StuartWhiteford.SQLServer.Product];
 
CREATE AGGREGATE [dbo].[AnnualisedProductQuarterlyData] (@input decimal(38, 10))
RETURNS decimal(38, 10)
EXTERNAL NAME [StuartWhiteford].[StuartWhiteford.SQLServer.AnnualisedProductQuarterlyData]
 
CREATE AGGREGATE [dbo].[AnnualisedProductMonthlyData] (@input decimal(38, 10))
RETURNS decimal(38, 10)
EXTERNAL NAME [StuartWhiteford].[StuartWhiteford.SQLServer.AnnualisedProductMonthlyData]