SQL. Running Totals.

Running totals... Accountants like them so much. But there is no way in Microsoft SQL Server to make an efficient query that calculates the running total. Why? What is running total? It is just the previous running total plus the current value. But T-SQL does not give any access to the previous rows. I searched the Internet for the running totals. For every link I clicked, I saw 2 methods to calculate running totals:

• join or subquery the table. Here, we have n2 operations
• use cursor and temporary table. Here, we have to insert into temporary table and then to join on the basis query.

So, both ways are not so fast as I required. But one might think that by using procedural languages, it may be done very fast.

Yes! .NET does help us since Microsoft added seamless support of .NET into the SQL Server!

The idea is to create a .NET function that can remember values of previous row and force desired order for the running total in SQL query.

First, we need a function to keep the previous running total. As far as SQL Server is a multithreaded environment, we should guaranty thread-safety and keep each running total in thread-related variable. It is a good idea to use CallContext class for this purpose.

using System;
using System.Runtime.Remoting.Messaging;

namespace RunningTotal
{
    public static class RunningTotalUtils
    {
        public static decimal RunningTotal(decimal currentValue)
        {
            object _lastTotal = CallContext.GetData("runningTotal");
            decimal lastTotal = _lastTotal == null ?
              0 : Convert.ToDecimal(_lastTotal);
            lastTotal += currentValue;
            CallContext.SetData("runningTotal", lastTotal);
            return lastTotal;
        }
    }
}

Now it’s time to create a T-SQL wrapper for the function:

CREATE ASSEMBLY RunningTotal
FROM 'C:\RunningTotal.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE FUNCTION RunningTotal (@amount decimal(18,2))
RETURNS decimal(18,2)
AS EXTERNAL NAME
  RunningTotal.[RunningTotal.RunningTotalUtils].RunningTotal;
GO

And... the fastest ever running totals come with the following SQL query:

select *, dbo.RunningTotal(Amount) Total from Transactions;

This is correct for the following table:

create table Transactions
(
   ID int identity primary key,
   Amount decimal(18,2)
);

It seems no one can do it faster.

In future, Microsoft will probably add support for windowing and this article becomes obsolete. For instance, in PostgreSQL 8.4, we may do the following:

select *, sum(Amount) over(order by ID) Total from Transactions;

The result will be the same.