Home Dashboard Directory Help
Search

OVER clause enhancement request - ROWS and RANGE window sub-clauses by Itzik Ben-Gan


Status: 

Closed
 as Fixed Help for as Fixed


277
1
Sign in
to vote
Type: Suggestion
ID: 254392
Opened: 1/28/2007 11:53:03 AM
Access Restriction: Public
Duplicates: 125053
0
Workaround(s)
view

Description

SQL Server 2005 introduced only partial support for the OVER clause. It is our strong believe that a more complete implementation of the OVER clause should be prioritized highly in consideration for future enhancements in SQL Server. A more complete implementation of the OVER clause can help in solving many common business problems with simpler, more intuitive, and faster solutions than available today and also substantially reduce the need for cursors.
The following paper details the feature enhancement requests:
http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc
The current feedback item addresses the OVER clause enhancement request - ROWS and RANGE window sub-clauses, discussed in the paper in section 4 item v.
Details
Sign in to post a comment.
Posted by Microsoft on 7/13/2011 at 4:39 PM
Hello Everyone,
Thanks for your feedback. We have now added support for window functions that is compatible with ANSI SQL:2008 standard. For framing using RANGE, we only support well-known bounds due to lack of INTERVAL type support. We will consider enhancing it in the future.
Below are the links to the documentation for latest CTP of SQL Server Denali:

http://msdn.microsoft.com/en-us/library/ms189461(SQL.110).aspx
http://msdn.microsoft.com/en-us/library/hh213234(v=SQL.110).aspx

--
Umachandar, SQL Programmability Team
Posted by TechVsLife2 on 7/11/2011 at 5:10 PM
It appears that Denali is expected to add it:
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI310

Will add "most of the missing windowing functionality (only some minor missing)":
Adds LAG, LEAD, FIRST_VALUE, LAST_VALUE etc.
around 49:00 in Ben-Gan's presentation.
Posted by Bryan St on 3/21/2011 at 8:33 PM
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 ROWS & RANGE functionality. The code relies on the ability to handle a sub-range of rows directly within a single SELECT statement via these window sub-clauses.

With no effective workarounds, this functionality is effectively unavailable.

Competitors already have this; after six-plus years it would be nice to see Microsoft step up to the plate on this one.
Posted by Martin Smith on 1/1/2011 at 2:35 PM
Any update on this? Looking at BOL for DENALI seems that the only enhancement to the OVER clause is FETCH/OFFSET which was easily achieved by using a CTE, ROW_NUMBER() and BETWEEN anyway.
Posted by WebSolProv on 10/20/2010 at 5:51 AM
It is almost three years since the last comment on this, and I understand that the next version of SQL Server is currently being worked on, so where are we? are we finally going to see the much needed ability (using enhancements to the existing windowing functions) such that running totals, previous row values and so forth can be implemented quickly and more importantly efficiently than the existing workaround methods.

Please Microsoft give us something to look forward to, an expected release date would be much appreciated?
Posted by Microsoft on 9/11/2007 at 4:50 PM
Note: This feedback is for a series of requests for OVER clause enhancement. These items will be looked at both individually and together in future feature design.

This feature unfortunately did not fit into our schedule for SQL Server 2008. Based on customer input, we had prioritized ORDER BY for aggregates and the ROW/RANGE feature, but in the end decided that in order for this feature to be truly useful, we needed to implement more functionality than time would allow. This is a top priority for the next release, and we continue to look forward to great suggestions from you
Sign in to post a workaround.