SQLCLR in SQL Server 2008: Multi-input user-defined aggregates

One thing that I didn't find in the BOL What's New page is some of the new SQLCLR functionality in SQL Server 2008. The first one that intrigued me is support of multi-input user-defined aggregates. Suppose I wanted to implement Oracle's COVAR_POP aggregate, an analytic function that returns the population covariance between two expressions. The signature is COVAR_POP(expr1, expr2) and I want the signature to stay the same in SQL Server.

All that I need to do this is to use the "template" for a .NET UDAgg struct/class, replacing the Accumulate method that take one parameter with a 2-parameter method, like this:

public void Accumulate([SqlFacet(Precision = 20, Scale = 10)] Nullable<decimal> Value1, 
                               [SqlFacet(Precision = 20, Scale = 10)] Nullable<decimal> Value2)
{
  // code here
}

My CREATE AGGREGATE DDL statement would change a bit:

CREATE ASSEMBLY multiparmagg FROM 'C:\temp\multiparmagg.dll'
go

CREATE AGGREGATE dbo.covar_pop(@expr1 decimal(20,10), @expr2 decimal(20,10))
RETURNS decimal(20,10)
EXTERNAL NAME multiparmagg.CovarPop;
go

To invoke:

create table dbo.test_covar (
  i1 decimal(20,10),
  i2 decimal(20,10)
);
go
— fill with data, then…
select dbo.covar_pop(i1, i2) from dbo.test_covar;

Happy aggregating.

2 thoughts on “SQLCLR in SQL Server 2008: Multi-input user-defined aggregates

  1. I’m a newbe and I think this is probably close to what I need, so maybe you can fill in the blanks…

    I need to create a "view" of my data that looks like this:

    VENDORID, SALES-THIS-YEAR, SALES-LAST-YEAR, SALES-DIFFERENCE, LBS-THIS-YEAR, LBS-LAST-YEAR, LBS-DIFFERENCE

    This would all come off of a single table ("MySalesTable") or be calculated (subtract).

    Since the year is non-deterministic, can I use a VIEW or AGGREGATE?

    Thanks,

    Bill Ross

  2. Hi Bill,

    You can create a view for this if you use this year and last year, because you can figure out this year and last year from the GETDATE function. A view is just like a "SQL macro", it will be expanded by SQL Server as though it were part of every query it participates in.

    If you’d like to pass in an arbitrary year and return a set of data, this can be accomplished with a single-statement table valued function. This really is (even the classification of its query plan reads) a VIEW into which you can pass parameters.

    Cheers,
    Bob

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.