Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Window Functions (OVER Clause) - Reuse of Window Definitions with WINDOW Clause

This item is related to: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391, only the former suggestion wasn't standard whereas the current one is. Therefore the current is preferred.

With several window functions that rely on the same window definition (or part of it), there's a lot of repetition of code. Standard SQL has a clause called WINDOW that allows naming a window definition or part of it, making it reusable.
For example, instead of:

SELECT empid, ordermonth, qty,
SUM(qty) OVER ( PARTITION BY empid
ORDER BY ordermonth
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW ) AS run_sum_qty,
AVG(qty) OVER ( PARTITION BY empid
ORDER BY ordermonth
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW ) AS run_avg_qty,
FROM Sales.EmpOrders;

You would write:

SELECT empid, ordermonth, qty,
SUM(qty) OVER W1 AS run_sum_qty,
AVG(qty) OVER W1 AS run_avg_qty,
FROM Sales.EmpOrders
WINDOW W1 AS ( PARTITION BY empid
ORDER BY ordermonth
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW );

31 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 173

<=-=Sep 27 2010 2:23PM=-=>

Hi Itzik,
Thanks for your feedback on WINDOW clause. We will consider it for a future version of SQL Server.


Umachandar, SQL Programmability Team

<=-=Mar 21 2011 8:27PM=-=>

I’m currently migrating an established application from another database platform to SQL Server. There’s a big, powerful chunk of the app missing due to the absence of the WINDOW clause functionality.

I wouldn’t even want to think of how to implement this type of processing in SQLCLR, if that’s even possible.

And if WINDOW is standard SQL, shouldn’t it be included in the Microsoft product? Competitors have it.

<=-=Mar 2 2012 1:18PM=-=>

Reuse of Window Definitions with WINDOW Clause
This has been rolled up into our “Window Aggregates Enhancements” DCR for future consideration. Thank you for reporting it. All the information you provided has been captured for future reference.

Thanks,
Marc Friedman

<=-=Nov 14 2017 3:33PM=-=>

This is particularly useful for me when I have to update a window definition in a complex query with reused windows.
It reduces the chance for error.

1 comment

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Steven Hibble commented  ·   ·  Flag as inappropriate

    I use the PERCENTILE window functions often. If I return quartiles or deciles, managing the window(s) can easily get out of hand. This would solve that while cleaning up the code.

Feedback and Knowledge Base