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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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?

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Erland Sommarskog ([email protected]) 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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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!

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)