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'
CREATE AGGREGATE dbo.covar_pop(@expr1 decimal(20,10), @expr2 decimal(20,10))
EXTERNAL NAME multiparmagg.CovarPop;
create table dbo.test_covar (
— fill with data, then…
select dbo.covar_pop(i1, i2) from dbo.test_covar;