While Sam Saffron did great work on it, he still didn't provide **recursive common table expression** code for this problem. And for us who working with SQL Server 2008 R2 and not Denali, it's still fastest way to get running total, it's about 10 times faster than cursor on my work computer for 100000 rows, and it's also inline query.<br>
So, here it is (I'm supposing that there's an `ord` column in the table and it's sequential number without gaps, for fast processing there also should be unique constraint on this number):
;with
CTE_RunningTotal
as
(
select T.ord, T.total, T.total as running_total
from #t as T
where T.ord = 0
union all
select T.ord, T.total, T.total + C.running_total as running_total
from CTE_RunningTotal as C
inner join #t as T on T.ord = C.ord + 1
)
select C.ord, C.total, C.running_total
from CTE_RunningTotal as C
option (maxrecursion 0)
-- CPU 140, Reads 110014, Duration 132
**<kbd>[sql fiddle demo](
[To see links please register here]
;**
**update**
I also was curious about this **update with variable** or **quirky update**. So usually it works ok, but how we can be sure that it works every time? well, here's a little trick (found it here -
[To see links please register here]
) - you just check current and previous `ord` and use `1/0` assignment in case they are different from what you expecting:
declare @total int, @ord int
select @total = 0, @ord = -1
update #t set
@total = @total + total,
@ord = case when ord <> @ord + 1 then 1/0 else ord end,
------------------------
running_total = @total
select * from #t
-- CPU 0, Reads 58, Duration 139
From what I've seen if you have proper clustered index/primary key on your table (in our case it would be index by `ord_id`) update will proceed in a linear way all the time (never encountered divide by zero). That said, it's up to you to decide if you want to use it in production code :)
**update 2** I'm linking this answer, cause it includes some useful info about unreliability of the quirky update - [nvarchar concatenation / index / nvarchar(max) inexplicable behavior](
[To see links please register here]
).