Groups > comp.databases.ms-sqlserver > #2210

Re: Custom window functions

Path	csiph.com!weretis.net!feeder8.news.weretis.net!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Custom window functions
Date	Tue, 23 May 2023 20:56:56 +0200
Organization	Erland Sommarskog
Lines	34
Message-ID	<XnsB00DD51AA11A2Yazorman@127.0.0.1> (permalink)
References	<20230522123254.38c0b664930a7bc595f60a3a@g{oogle}mail.com>
MIME-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	dont-email.me; posting-host="3e7fa41b7a203627e06f35c64e192823"; logging-data="2807645"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19OgAK17asddhDLS/Y0rE+C"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:zAFT+jSSRn/35WVe2mokm0S92ak=
Xref	csiph.com comp.databases.ms-sqlserver:2210

Show key headers only | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Custom window functions Anton Shepelev <anton.txt@g{oogle}mail.com> - 2023-05-22 12:32 +0300
  Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-23 20:56 +0200
    Re: Custom window functions Anton Shepelev <anton.txt@g{oogle}mail.com> - 2023-05-25 18:38 +0300
      Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-25 21:27 +0200
        Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-26 21:00 +0200
          Re: Custom window functions Anton Shepelev <anton.txt@gmail.moc> - 2023-05-26 22:55 +0300

csiph-web