Tuesday, 10 April 2012

T-SQL Tuesday #029 - Analytic Functions in SQL Server 2012

I took the last few days to review the new features of SQL Server 2012 in preparation for the beta exams  I will sit very soon. About many of them I read before but some still came as a nice surprise (better now than on the exam I suppose).

If I am to choose one useful feature of SQL Server 2012 to write about today it must be the Analytic Functions in T-SQL and improved OVER clasue. The LAG, the LEAD and the OVER which I would call a sapper tools for dealing with time bomb coding techniques, especially the triangular joins. Let me present

   LAG (scalar_expression [,offset] [,default]) 
      over ([partition_by_clause] order_by_clause)
  LEAD (scalar_expression [,offset] [,default]) 
      over ([partition_by_clause] order_by_clause)

They allow you to access the previous and next rows in the data set. And the improved OVER

  OVER ([ <ROW or RANGE clause> ])

which can be used with most of the aggregate functions to further limit the rows within the partition by specifying start and end points.

To see how effective they can be let's use AdventureWorks2012 and try to see the sales staff performance over time. I want to see: the value of each sale, how that value compares to previous value, what is the running average of the last 5 sales and what is the total value of sales to date for any salesman.

If we were to code the solution in T-SQL before 2012 we would probably come up with something like that

which could produce an execution plan like so
and require 11419 scans, only 7,867,691 logical reads and 190,914 ms CPU time on my machine.

After using the new time bomb removal kit

not only the code looks better but the execution plan is simpler
and it requires 1 scan, 689 logical reads and only 62 ms. That is 3030 times faster.

Obviously even before one could use SSIS to move data to SSAS and then use features of MDX to achieve the same but I really like the simplicity of doing it in OLTP. I also think it will be a very useful tool to heal a lot of performance issues in legacy applications offering big gains with relatively small costs.

This post is a part of the T-SQL Tuesday #029 hosted by Nigel Sammy. As only recently I finally decided to take a bit more active part in the on-line community and this postlet is my very first T-SQL Tuesday contribution so any feedback will be much appreciated. 


  1. Excellent explanation! Thanks a lot it really helped to understand the analytic functions better.

  2. It will be great if you can give some explanation with examples regarding ranking, partition, First_value and Last_value