T-SQL features in SQL Server 2012

This post discuss about new T-SQL features in SQL Server 2012. The features which I am going to discuss here are Window Functions. Windowing and additional analytic functions are very cool and they are replacing cursors in T-SQL. Cursors are still available for use but windowing functions give better performance over cursors.

What is window function?

Window function is a function that applied against a set of rows. It allows you to do grouping and also allows you to see individual rows in one query.

Usually when you do group by you can only see the things that are in group by and aggregates. Suppose if I want to see sales group by sales person and also want to see individual rows that sales person sold then You can achieve this in SQL Server 2005 using over clause. Over clause always let you return groups and individuals in same row.

In 2005 we also have ranking functions using over clause. You can also use ranking functions with aggregate functions. What you do not have in 2005 is you did not have order by ability or selecting a window frame for result set.