Systems Engineering and RDBMS

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: