This month, T-SQL Tuesday is being hosted by Nigel Sammy. Since the RTM of SQL Server 2012 just occurred, he wants to know: “What do you think is a useful feature of SQL Server 2012?”
Well, if you’ve been following my blog, then this should not be much of a surprise to you. My #1 useful feature of SQL Server 2012 is the enhancements made around the OVER clause.
Why the OVER clause?
One thing that I have been following for a few years now is the various methods of performing running totals – a calculation of the total from the previous row plus a value from the current row. I’ve seen several methods, from cursors to utilizing clustered index scans to self-joins. There are pros and cons with using each method, but regardless of how you did it, there were shortcomings to overcome.
How does the OVER clause solve this?
When Microsoft introduced the OVER clause in SQL Server 2005, its use in aggregate functions was pretty limited – you could use the aggregate function with just the PARTITION BY clause. In SQL Server 2012, with the enhancements to the OVER clause following the ANSI standard, we can add to this the use of the ORDER BY clause, and the ROWS|RANGE clause. With this combination, a running total (heck, a running aggregation on most of the aggregate functions) is extremely easy – specify when the aggregation starts over with the PARTITION BY clause; use the ORDER BY clause to specify what order to evaluate the rows; and use the ROWS|RANGE clause to specify which rows to use in the calculation. By specifying the first row through the current row, you have a running aggregation. My testing shows that it is indeed very fast. And testing by others shows this also.
But wait! – There’s More
The new Analytic functions also require the OVER clause, and some of them are bound to be extremely useful as well. I’ve covered the Analytic Functions in a series on my blog, but the ones I find the most promise for are LAG/LEAD, and FIRST_VALUE/LAST_VALUE.
Conclusion
I think that the OVER clause enhancements, plus the new analytic functions, are a game changer. They are fast; set-based; documented; simple to use. While there might be some methods out there that can still perform slightly better, I see these enhancements taking over. The only con… you need SQL Server 2012.
