Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Running Total with T-SQL 2012

  • 27 November 2013
  • Author: Kathi Kellenberger
  • Number of views: 10681

Writing a T-SQL statement that calculates a running total is really easy with SQL Server 2012. But, if you are not careful, you will see less than stellar performance.

Back in 2005, Microsoft began adding window functions to T-SQL. I’ve also seen this called windowed or windowing functions. Either way, this has nothing to do with the Windows operating system. If you have used ROW_NUMBER(), you are using a window function. The window is the set of rows that the function operates on. The window is defined using the OVER clause. I have written several blogs about these functions. A list appears at the end of the post.

Microsoft gave us ROW_NUMBER, RANK(), DENSE_RANK() and NTILE()as well as window aggregates in 2005. Window aggregates are your favorite aggregate functions followed by the OVER clause. For example, you could write a query like this to give you the total sales along with the details for each customer by using the Partition By option in the OVER clause:

SELECT CustomerID, SalesOrderID, OrderDate, TotalDue,
 SUM(TotalDue) OVER(Partition by CustomerID) AS TotalCustomerSales
FROM Sales.SalesOrderHeader;


There is also an ORDER BY option for the OVER() clause that is not supported for window aggregates previous to 2012. Starting with 2012, you can use the ORDER BY option to create a running total like this:

SELECT CustomerID, SalesOrderID, OrderDate, TotalDue,
 SUM(TotalDue) OVER(Partition by CustomerID Order By SalesOrderID) AS RunningTotal
FROM Sales.SalesOrderHeader;


The problem with this method is that the performance can be poor. When I use SET STATISTICS IO ON, I see that a worker table was created and then scanned 50,525 times!

Another enhancement added in 2012 is called framing. With framing, you can define the window even more granularly by using ROWS and RANGE. By default, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That means do the calculation on all of the previous rows and up to the current row. Unfortunately, the RANGE framing option creates a worktable on disk which causes poor performance. Instead, if you specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, the optimizer will create the worktable in memory. This query produces the same results, but with much better performance:

SELECT CustomerID, SalesOrderID, OrderDate, TotalDue,
 SUM(TotalDue) OVER(Partition by CustomerID Order By SalesOrderID
FROM Sales.SalesOrderHeader;


There are also other differences between ROWS and RANGE. ROWS refers to a physical offset from the current row, and RANGE refers to a logical difference from the current row. In the case of the running total, if there are duplicates in the ORDER BY column in the OVER clause, you will see slightly different results than when using ROWS.

The SQL standard contains much more functionality than was implemented for RANGE in 2012. There are no plans to include this functionality in SQL Server 2014 that I have heard. For now, I suggest always specifying ROWS unless you are certain that RANGE is the only way to get the results you need.

Here are the other window function posts I have written:

What is a Window Aggregate Function?


Categories: SQL Server
Rate this article:
No rating

Please login or register to post comments.