I've been trying out the windowing extensions (the OVER clause extensions and friends) with different kinds of functions (aggregate functions, first_value, last_value() etc), and thought I'd use a SQLCLR aggregate. After all, they're just "regular" aggregates, right? You can't build a user-defined ranking aggregate in SQLCLR, so they can't be confused with one of those. And you can use 'em (user-defined aggregates) with the OVER() clause in previous versions of SQL Server. Unlike the CHECKSUM function, that the BOL points out which is the only aggregate that can't be with OVER() at all, though CHECKSUM_AGG doesn't seem to have this problem.
— these worked before
select *,
dbo.covar_pop(i1, i2) over()
from test_covar
select *,
dbo.covar_pop(i1, i2) over(partition by i1)
from test_covar
Hope I was hopeful. Well it didn't quite come out the way I'd hoped…
select *,
dbo.covar_pop(i1, i2) over(partition by i1 order by i2)
from test_covar
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'order'.
A built-in aggregate worked fine.
select *,
avg(i2) over(partition by i1 order by i2)
from test_covar
So it appears that SQLCLR user-defined aggregates are specifically being "called out", ie, don't work with the new Denali functionality. Perhaps the state machine in the SQLCLR UDA architecture doesn't support/scale with the intermediate ordered results. We've got some nice new built-in distribution functions (like , but I'd like to use my covar_pop (and others) with the full functionality of windowing, rather than having to wait. I've put in a connect item for it, we'll have to wait and see.
Update: Feedback from my connect item (that was fast…) is that windowing over SQLCLR UDAs "didn't make the cut in Denali. Maybe next time".
@bobbeauch