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).
And let me be the first to say I realize this is a feature incompatible with set-based-thinking.
Comment by Michael J. Swart — April 22, 2010 @ 10:59 pm
[…] Sometimes the grass is greener… – Yeah right. […]
Pingback by Something for the weekend – SQL Server links for the week 23/04/10 | John Sansom - SQL Server DBA in the UK — April 24, 2010 @ 2:12 pm
If you aren’t afraid to use the occasional undocumented feature, take a peek at the following…
http://www.sqlservercentral.com/articles/T-SQL/68467/
Comment by Jeff Moden — August 13, 2010 @ 6:40 pm
That’s awesome Jeff. Well worth the read.
I’m not shy of any tricks. I love that I’ve never heard of a quirky update before. Learning something new is always a bit exciting. You’ve helped me check off my “learn one new thing every day” for today.
Part of me wishes that the solution to this problem was free of the “undocumented” or “hacky” adjectives.
Comment by Michael J. Swart — August 14, 2010 @ 7:01 pm
Glad I could help, Michael, and thanks for the honorable mention for the Triangular Join article.
Agreed. The “Quirky Update” is most useful and I wish that MS would just come out and say that it works. People have been using it in Sybase (GrandDad of the Rushmore Engine, IIRC) since long before MS procurred SQL Server.
Comment by Jeff Moden — August 15, 2010 @ 10:28 pm
Woo hoo! The grass is getting greener. Denali will have an implementation of this feature.
http://msdn.microsoft.com/en-us/library/hh213125%28v=SQL.110%29.aspx
Comment by Michael J. Swart — July 20, 2011 @ 11:25 am
Yes it DOES! Its faster than a cursor, too! I don’t know what problems they had with it, but premliminary testing shows that Running Totals using the new methods in Denali are still 25 times slower than the quirky update. In real terms, it takes 2.2 seconds for a quirky update to do its thing on a million rows for two different columns. It takes 57 seconds to do the same thing using the new functions. Still, that’s a bit more than twice as fast as the fastest cursor you could write and it IS a documented feature.
–Jeff Moden
Comment by Jeff Moden — July 20, 2011 @ 2:41 pm