Running totals... Accountants like them so much. But there is no way in MS SQL Server to make efficient query that calculate running total. Why? What is running total? It is just previous running total plus current value. But T-SQL does not give an access to previous rows. I search the Internet for the running totals. Every link I click 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 need to. But one can feel that if to use 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 previous running total. As far as SQL Server is multithread environment we should guaranty thread-safety and keep each running total in thread-related variable. It is good idea to use CallContext class for this purpose.
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;
Now it’s time to create a T-SQL wrapper for the function:
CREATE ASSEMBLY RunningTotal
WITH PERMISSION_SET = UNSAFE;
CREATE FUNCTION RunningTotal (@amount decimal(18,2))
AS EXTERNAL NAME
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,
It seems no one can do it faster.
In future Microsoft will probably add support for windowing and this article become 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.