|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.
Background on Over clause
Suppose we want to have sales person bonus group by territory then we can start query as below
Basically I want to have individual information and aggregated value on same row… If you include additional columns in select clause and it complains those columns are not in group by clause!
You can rewrite the above query without group including the regular columns
Even you can use partition by state in above query to get groups without using group by. What if you need lot of aggregates? user over clause rather writing sub queries, over clause optimizes the performance.
If you look at the performance precautions over clause vs bunch of sub queries 65% for sub query and 35% for over clause. so over clause
is twice as good and twice as fast.
so far nothing new we all know these features are there
In earlier versions of SQL you can Aggregate functions with over clause and partition clause but you can not use order by! Order by is only for ranking aggregates in earlier version.
Now in SQL 2012 you have Order and window frame clauses with aggregates
Additional analytical functions
Inverse distribution functions
Windows Function Uses
Windows functions can eliminate cursors including
Find percentages within a group
Performance and easy-of-use advantage over
Windows Function specification is always used with over clause and there are three clauses that can be used with the over clause
1. Partition by clause is used to divide the rows into groups.
2. Order by clause specifies ordering within a partition
3. Frame clause restrict the function to subset of rows.
Rows 2 preceding is a specification of window clause. It says I want two preceding row and also i want the current row.Basically you are having a window frame
that shows 3 month running average.
Result of the above query looks as below
The above query is another variation of windows function and it returns sales for previous month, current month and next month.
If you want the cumulative sum from the beginning then you can use ROWS unbounded preceding clause in windows function.
The result of the query
LAG Function Usage
LAG Function is a function which gets the specific values that you specify in first argument and number of rows back as second argument and can have null as default third argument.
In LAG you no need to specify the window frame because it is part of the definition of the LAG. You can also specify the default value in place of null. Result as below
If you want to compare sales of this month compared to first month then you can do with t-sql in SQL 2012. You no need to go to analysis services or power-pivot.
Result as below
This table shows what kind of function that you can use with the over clause.
Next post discuss the remaining features so stay tune…
Reference Tech-Ed talk from Bob Beauchemin.
|Share this post :|