Update July 20, 2011: Read the comments, SQL 2012 Will support these functions!
Today I really wished I was working in an Oracle environment. I came across a problem where I really could have used Oracle’s LAG or LEAD functions.
Greener on the other side
The LAG() and LEAD() Functions
They’re functions which return a value based on the previous or next record in an ordered set of records. While there are ways to mimic the functionality, the performance seems to suffer because either a cursor is used or a set of data is joined to itself (leading to something Jeff Moden calls Triangular Joins).
When I searched for this problem online, I was not surprised to find Itzik Ben Gan’s name. He and Sujata Mehta present their case about why Microsoft should support these features. Download and read it!
Afterwards I encourage readers to visit Itzik’s enhancement request at the Microsoft Connect site and give it another up-vote. This item deserves to be up-voted. Not just for the simplified syntax (i.e. faster development) but for the potential for optimization that it enables (i.e. faster performance).
The current state of the issue request is that Microsoft is “looking into it for a future release” (which seems to be a too-common reply at Connect lately).
