Home Dashboard Directory Help
Search

OVER clause enhancement request - LAG and LEAD functions by Itzik Ben-Gan


Status: 

Closed
 as Fixed Help for as Fixed


326
2
Sign in
to vote
Type: Suggestion
ID: 254388
Opened: 1/28/2007 11:43:39 AM
Access Restriction: Public
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 - LAG and LEAD functions, discussed in the paper in section 4 item ii.
Details
Sign in to post a comment.
Posted by Microsoft on 7/13/2011 at 4:37 PM
Hello Itzik,
Thanks for your feedback. We have now added support for LAG/LEAD functions and other window functions support that is compatible with ANSI SQL:2008 standard.
Below are the links to the documentation:

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 Peter11111 on 7/13/2011 at 2:05 AM
In Denali CTP3 see
LAG : http://msdn.microsoft.com/en-us/library/hh231256%28v=SQL.110%29.aspx
LEAD : http://msdn.microsoft.com/en-us/library/hh213125%28v=SQL.110%29.aspx
Posted by J King MHS on 12/9/2010 at 8:49 AM
Is this kind of window function currently being considered for Denali?
Posted by TomThomson on 2/23/2010 at 7:46 AM
I agree completely with muudkin. And I disagree strongly with Itzik's assertion that LAG and LEAD are simpler and more intuitive - using Garnge expressions like BETWEEN seems to me more intuitive and simpler, and I feel it's better to have one simple way of doing things. Everyone will want to use Range expressions anyway, since they are more flexible than LAG and LEAD, so they will learn to understand that syntax - LAG and LEAD are just an added thing to cope with.
Posted by Paul White NZ on 2/2/2010 at 6:36 PM
Personally, I would prefer the ability to implement these sorts of analytic functions to be part of a broader SQLCLR facility.

SQLCLR already provides us with a way to write CLR UDAs, UDTs, TVFs, and SPs - just add an interface to the Segment/Sequence Project operators already used for the T-SQL analytic functions :c)

SQLCLR analytic functions (similar to the Oracle implementation) would give us maximum flexibility - though it would certainly be nice if a few of the core functions (as described in this item) were implemented in SQLCLR by Microsoft - similar to the Geography and Geometry types (but using the new SQLCLR Analytic Functions, rather than UDTs).
Posted by Phil Brammer on 11/20/2009 at 6:51 AM
ROWS BETWEEN ... would be a HUGE improvement, because to do the same without that feature generates quite a bit of code. Most notably on Teradata where this feature exists (ROWS BETWEEN, not LEAD/LAG), we saved quite a bit of time and energy and were able to do some great calculations by simply using this construct.
Posted by SQLpro on 11/6/2009 at 1:45 AM
LEAD and LAG are now part of ISO standard definition of SQL since SQL:2008 with also ntile, first value, last value and nth value

A +
Posted by kspinka on 1/9/2008 at 3:08 PM
It would be very disappointing if analytical windows such as LEAD/LAG were not supported in SQL Server 2008. I hope the development team reconsiders the value of these windowing features.
Posted by Microsoft on 12/13/2007 at 3:30 PM
Dear Itzik.

Thanks a lot for your feedback... and the strong voting support for this feature. Indeed, as you know, I am very much in favor of extending our functionality in this area. For a variety of reasons we did not get this into SQL Server 2008, but we are certainly looking into it for a future release.

Keep the votes and comments coming...
Michael
Posted by Bjorn D. Jensen on 7/31/2007 at 2:09 AM
I totally agree with Itzik!
Posted by Mark Yudkin on 2/1/2007 at 2:54 AM
I think it would be better to concentrate on getting the ANSI standard implemented, rather than third party extensions that have an alternative solution in the standard.

SQL Server doesn't implement the ROWS subclause, nor does it support the ORDER BY subclause with aggregate functions. We should prioritize implementation of these features.
Posted by Itzik Ben-Gan on 1/29/2007 at 2:30 PM
Note that LAG and LEAD are not standard, rather proposed as extensions to the standard, similar to their implementation in Oracle. The expression:

LAG(qty, 1, NULL) OVER(PARTITION BY empid ORDER BY ordermonth)

Can be expressed with a standard use of OVER based calculations like so:

MIN(qty) OVER (PARTITION BY empid ORDER BY ordermonth
             ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)

But we feel that the use of LAG and LEAD in such cases is simpler and more intuitive.
Sign in to post a workaround.