Calculating a Running Total - SQL Server

{outline|||<1> - }

There are three fundamental ways to calculate running totals in SQL Server. Prior to SQL Server 2005, you had to use a cursor. SQL Server 2005 introduced the ROW_NUMBER() function, and thus a new way of calculating running totals. For larger rowsets, performance can be improved over the technique that uses the ROW_NUMBER() function by using a SQL-CLR table-valued function within an UPDATE statement. The exact borderline between "small" rowset and "large" rowset (and therefore which technique you should use, the ROW_NUMBER() function or a SQL-CLR), depends on your specific situation.

Sample code for the three techniques is shown below.

SQL Server 2000: Using a Cursor

The following code is how you had to do this in SQL 2000:

declare @runningSum money
set @runningSum = 0

declare @SignedAmount money, @NewBalance money

declare @AccountId uniqueidentifier
set @AccountId = '00000000-0000-0000-0000-000000000000'

declare MyCursor cursor for
SELECT top 100 percent
     t.NewBalance
    ,SignedAmount = case when t.DepositFlag = 1 then t.Amount else -1 * t.Amount end
FROM 
    AccountTransaction t
    inner join Register r
        on t.RegisterId = r.RegisterId
    inner join Account a
        on r.AccountId = a.AccountId
where 1=1
    and a.AccountId = @accountId
order by
     r.StartDate
    ,t.PageNumber
    ,t.LineNumber
for update

open MyCursor

fetch from MyCursor into @SignedAmount, @NewBalance

while @ @fetch_status = 0 begin

    set @runningSum = @runningSum + @SignedAmount

    update AccountTransaction
    set NewBalance = @runningSum where current of MyCursor

    fetch from MyCursor into @SignedAmount, @NewBalance

end

close MyCursor
deallocate MyCursor

SQL Server 2005, Small Rowsets: Using ROW_NUMBER()

DROP TABLE #totals
SELECT 
     ROW_NUMBER() OVER (ORDER BY id) rn, 
     name, 
     country, 
     sales 
INTO #totals
FROM emp;
WITH rt (rn, name, country, sales, runtot)
AS(
     SELECT rn, name, country, sales, sales
     FROM #totals
     WHERE rn = 1
     UNION ALL
     SELECT t.rn, t.name, t.country, t.sales, t.sales + rt.runtot
     FROM #totals t
     INNER JOIN rt 
          ON t.rn = rt.rn + 1
   )
SELECT * FROM rt
DROP TABLE #totals

SQL Server 2005, Large Rowsets: Using SQL-CLR

Better performance can be achieved using a SQL-CLR table-valued function within an UPDATE statement