User Defined Aggregate Function in T-SQL instead of .net assembly?

Question

aggregate functions are useful, e.g.:

select FinancialYear, PLFolder, Sum(PLDelta) SumDelta 
from PL group by FinancialYear, PLFolder

however, sometimes I need do more complex calculation, such as, calculating the volatility.

This could be done by a User Defined Aggregate Function, programmed in C#, compiled as a CLR assembly, and then imported into MSSQL.

Is it possible to do it in pure T-SQL?

Isn't volatility easily calculated with the other aggregate functions? — Pieter Geerkens, Mar 15 '13 at 04:10
this is just an example, for example, to calculate a quantile number, but instead of following mssql built-in "top 5%" logic, requiring some interpolation... — athos, Mar 15 '13 at 06:21

score 8 · Accepted Answer · answered Mar 15 '13 at 08:50

8

No.

The only way to write custom aggregates is through SQLCLR. There are technical reasons behind this limitation, primarily related to the need of an aggregate to have state.

answered Mar 15 '13 at 08:50

Remus Rusanu

288,378
40
442
569

User Defined Aggregate Function in T-SQL instead of .net assembly?

1 Answers1