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.

Background on Over clause

Suppose we want to have sales person bonus group by territory then we can start query as below

   1: SELECT st.Name as [Territory Name], AVG(s.Bonus) as [Avg Bonus] 

   2: FROM [Sales].[SalesPerson] s

   3: join [Sales].[SalesTerritory] st

   4: on s.TerritoryID = st.TerritoryID

   5: GROUP BY st.Name

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

   1: SELECT st.Name as [Territory Name],

   2:        st.[Group], st.TerritoryID,

   3:        AVG(s.Bonus) over() as [Avg Bonus] 

   4: FROM [Sales].[SalesPerson] s

   5: join [Sales].[SalesTerritory] st

   6: on s.TerritoryID = st.TerritoryID

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

Offset functions

– LAG/LEAD

– FIRST_VALUE/LAST_VALUE

 Additional analytical functions

  Distribution functions

        Percent_Rank, CUME_DIST

  Inverse distribution functions

     Percentile_Cont,Percentile_Disc

Windows Function Uses

Windows functions can eliminate cursors including

    Running totals

    Moving averages

    Moving sums

    Median values

    Find percentages within a group

Performance and easy-of-use advantage over

    Grouped queries

    Sub queries

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.

Example 1

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

Example 2

The above query is another variation of windows function and it returns sales for previous month, current month and next month.

Example 3

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.

This is just an example analytical power of t-sql.

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 :