\nI'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.\n<\/p>\n
\n— these worked before
\nselect *,
\ndbo.covar_pop(i1, i2) over()
\nfrom test_covar\n<\/p>\n
\nselect *,
\ndbo.covar_pop(i1, i2) over(partition by i1)
\nfrom test_covar\n<\/p>\n
\nHope I was hopeful. Well it didn't quite come out the way I'd hoped…\n<\/p>\n
\nselect *,
\ndbo.covar_pop(i1, i2) over(partition by i1 order by i2)
\nfrom test_covar\n<\/p>\n
\nMsg 156, Level 15, State 1, Line 2
\nIncorrect syntax near the keyword 'order'.\n<\/p>\n
\nA built-in aggregate worked fine.\n<\/p>\n
\nselect *,
\navg(i2) over(partition by i1 order by i2)
\nfrom test_covar\n<\/p>\n
\nSo 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.\n<\/p>\n
\nUpdate: Feedback from my connect item (that was fast…) is that windowing over SQLCLR UDAs "didn't make the cut in Denali. Maybe next time".\n<\/p>\n
\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"
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. […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,38],"tags":[],"class_list":["post-543","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sqlclr"],"yoast_head":"\n