Custom window functions

[Anton Shepelev]

Hello, all.

Does MSSQL support custom window functions? For example,
suppose I want to implement exponential smoothing in .NET
and register it as a window function in MSSQL -- is that
possible?

--
() ascii ribbon campaign -- against html e-mail
/\ www.asciiribbon.org -- against proprietary attachments

[Erland Sommarskog]

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> Does MSSQL support custom window functions? For example,
> suppose I want to implement exponential smoothing in .NET
> and register it as a window function in MSSQL -- is that
> possible?
>

Depends on what you want to achieve. This works:

SELECT object_id, name,
dbo.integerlist(column_id) OVER(PARTITION BY object_id)
FROM sys.columns
ORDER BY object_id, column_id

dbo.integerlist is a user-defined aggegrate implemented in C#.

But when I tried:

SELECT object_id, name,
dbo.integerlist(column_id) OVER(PARTITION BY object_id
ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM sys.columns
ORDER BY object_id, column_id

I got

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'ORDER'.

The error is a little surprising. But presumably there are different parse-
trees for built-in and user-defined functions. And supposedly when they
introduced windowed aggregates in SQL 2012, they only updated the parse
tree for tbe built-ins. Which opens the question what would happen if
they fixed the parser...

[Anton Shepelev]

Erland Sommarskog to Anton Shepelev:

> > Does MSSQL support custom window functions? For
> > example, suppose I want to implement exponential
> > smoothing in .NET and register it as a window function
> > in MSSQL -- is that possible?
>
> Depends on what you want to achieve. This works:
> SELECT object_id, name,
> dbo.integerlist(column_id) OVER(PARTITION BY object_id)
> FROM sys.columns
> ORDER BY object_id, column_id
>
> dbo.integerlist is a user-defined aggegrate implemented in
> C#.

Thanks, I didn't know at least this was possible.

> But when I tried:
>
> SELECT object_id, name,
> dbo.integerlist(column_id) OVER(PARTITION BY object_id
> ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
> FROM sys.columns
> ORDER BY object_id, column_id
>
> I got
>
> Msg 156, Level 15, State 1, Line 3
> Incorrect syntax near the keyword 'ORDER'.
>
> The error is a little surprising. But presumably there are
> different parse- trees for built-in and user-defined
> functions. And supposedly when they introduced windowed
> aggregates in SQL 2012, they only updated the parse tree
> for tbe built-ins.

Surprising indeed, nor do I think this unexpected difference
is documented, seeming like a bug. Why should the parser
care to distinguish between built-in and user-defined window
functions?

> Which opens the question what would happen if they fixed
> the parser...

The expected behavior does not seem difficult to predict...

Now that ORDER BY is not supported for windows with user-
defined aggregate functions, no sort of sequential time-
series analysis is possible, so I wrote my own using a
cursor:
https://pastebin.com/raw/iLVNXQ1m

Do you think a more convenient interface is possible?

[Erland Sommarskog]

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:

> Surprising indeed, nor do I think this unexpected difference
> is documented, seeming like a bug.

The Docs are smart enough to be sufficiently vague:

Depending on the ranking, aggregate, or analytic function used with the
OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not
be supported.

So it certainly permits for this exception.

But I could certainly argue that it is a bug in that the error
message should be semantic, and not a parsing error.

But now for some interesting news. In SQL 2022, they introduced the
WINDOW clause:

SELECT object_id, column_id, SUM(column_id) OVER MyWindow
FROM sys.columns
WINDOW MyWindow AS (PARTITION BY object_id

ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

ORDER BY object_id, column_id

This is useful when you want to use the same Windows clause for multiple
columns in the same query, as you only have to define it in one place.

So what about this?

SELECT object_id, column_id, dbo.integerlist(column_id) OVER MyWindow
FROM sys.columns
WINDOW MyWindow AS (PARTITION BY object_id

ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

ORDER BY object_id, column_id

Yes, it runs and returns the correct result.

However, I need to add a caveat here: The fact that this runs might
be a bug. I seem to recall that there is something about CLR
aggregates and ordering. That is, if this runs but produces an
incorrect result, this is not good. Then again, my aggregate is
supposed to return an ordered result, and it seems to do with my
test query. But that may be due to chance.

I will need to bring this up with some people at Microsoft.

[Erland Sommarskog]

Erland Sommarskog (esq...@sommarskog.se) writes:
> However, I need to add a caveat here: The fact that this runs might
> be a bug. I seem to recall that there is something about CLR
> aggregates and ordering. That is, if this runs but produces an
> incorrect result, this is not good. Then again, my aggregate is
> supposed to return an ordered result, and it seems to do with my
> test query. But that may be due to chance.
>
> I will need to bring this up with some people at Microsoft.
>

An MVP colleague was kind to point me to
https://learn.microsoft.com/en-
us/dotnet/api/microsoft.sqlserver.server.sqluserdefinedaggregateattribute.is
invarianttoorder?view=sqlclient-server-dotnet-1.0
which suggest that there is something hiding here.

[Anton Shepelev]

Erland Sommarskog:

> An MVP colleague was kind to point me to
> https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqluserdefinedaggregateattribute.isinvarianttoorder

> which suggest that there is something hiding here.

It being "reserved for future use", the hidden thing is the
latent potential for arbitrary time-series analysis via
cusom aggreage functions!