OVER clause – much needed enhancements
Posted by decipherinfosys on February 23, 2011
For those of us who work in both Oracle and SQL Server, it is always a natural reaction to look for something equivalent in the other RDBMS in our day to day work. One of the areas in which Oracle has excelled far ahead of SQL Server has been it’s rich support for analytics. OVER clause capabilities is just one of those things. MVP Itzik has posted several suggestions/requests on connect for such enhancements – you can see them here. It also has links from other readers as well which point to the rich functionality of Oracle and DB2 in that regard. You should also read this whitepaper from Itzik and Sujata on this topic.
As per this update on connect, these did not make it in SQL Server 2008 and might be included in the next release – Denali.
This came up again when a client who is responsible for releasing their product on Oracle as well as SQL Server needed to simulate some of that rich analytic function capability in SQL Server. There are always workarounds available and we have covered some of them in our previous blog posts (example – for first_value(), last_value() workaround) but it is time that SQL Server had that support now.