SQL Server 2012 Running Totals

SQL server has sometimes come under fire with the Oracle vs SQL Server debate because of the lack of some of the more advanced (and less used) functionality that is outlined in the SQL ANSI standards.   An example of this is Window Functions, which became an ANSI standard under the ANSI:2003 revision.  A Window function is an aggregate function that can be applied to a subset of a full set of data.  Now this can be achieved in current versions of SQL Server (2008 R2 and its predecessors), but not using Window Functions and therefore has a performance implication.  There are about 3 or 4 approaches to achieving a running total in SQL Server prior to the 2012 version, however none particularly elegant.

For my following example I will outline how to perform a running total using AdvertureWorks2008 sample data.  I will remind us of 1 of the method’s (probably the most common) of how we used to do running totals prior to SQL Server 2012, and then show how to do the same running total using a SQL Server 2012 Window Function.  To set the scene, we will be looking for a running total of Line Items for a given Order.  This may be kind of query you may wish to write to generate an invoice with a running total on it.

To begin with lets look at the more traditional query:

SELECT
A.SalesOrderID,
A.SalesOrderDetailID,
A.LineTotal,
SUM(B.LineTotal)
FROM
Sales.SalesOrderDetail  AS A
CROSS JOIN Sales.SalesOrderDetail AS B
WHERE
B.SalesOrderDetailID <= A.SalesOrderDetailID
AND
A.SalesOrderID = B.SalesOrderID
GROUP BY
A.SalesOrderID,
A.SalesOrderDetailID,
A.LineTotal
ORDER BY
A.SalesOrderID,
A.SalesOrderDetailID,
A.LineTotal

This simply works by self joining up to certain point, so works fine for any data that can be ordered easily, as running totals usually are this method usually suffices.

The following query uses the new Window function in SQL Server 2012:

SELECT
SalesOrderID,
SalesOrderDetailID,
LineTotal,
SUM(LineTotal)
OVER     (PARTITION BY
SalesOrderID
ORDER BY
SalesOrderDetailID) AS OrderRunningTotal
FROM
Sales.SalesOrderDetail
ORDER BY
SalesOrderID,
SalesOrderDetailID,
LineTotal

You will instantly notice that the second query is far more elegant, and more simplistic to understand.  In short, in this query, we are telling SQL server to Sum the “Line Total” over an ordered partition of the data.  Both queries return the same result, however it becomes interesting when we look at the execution plans, relative to each other.  This is shown below (apologies for the size of these):

What is important to note that the second (bottom) plan is smaller, and simpler.  the other, very significant point is that the Query Cost (relative to batch) is a whopping 97% for the old method of running totals, meaning that the new Windowing Functions are far more efficient.

In summary, I believe that this is an example of where SQL server is becoming a firm competitor to some of its perceived rivals, its one of the few points that can be raised as a valid point in the argument of SQL Server vs Oracle, but not any more!