Running total

[simon]

I can get total of sales by month by using update to local variable.
Example:

DECLARE @totalTbl TABLE (monthID tinyInt, sales int, salesSum int)

DECLARE @RunningSum int
SET @RunningSum= 0

INSERT INTO @totalTbl
SELECT monthID, sales, null
FROM orders
ORDER BY monthID

UPDATE @totalTbl
SET @RunningSum = salesSum = @RunningSum + sales
FROM @totalTbl

SELECT * FROM @totalTbl

It works. But the problem is that UPDATE statement doesn't guarantee
the order of update. I can't say:

UPDATE @totalTbl SET @RunningSum = salesSum = @RunningSum + sales
FROM @totalTbl
ORDER BY monthID

Is there some difference if I put clustered index on table variable:
DECLARE @totalTbl TABLE (monthID tinyInt primary key, sales int,
salesSum int)

I guess than this way update will be always ordered by monthID, but I
need some confirmation :)

br,
Simon

[Erland Sommarskog]

Post by simon
ORDER BY monthID
salesSum int)
I guess than this way update will be always ordered by monthID, but I
need some confirmation :)

I believe that there is someone on SqlServerCentral.com who has gone along
way to find a way that is "guaranteed" to work. And maybe it works in
practice. However, you will *never* get any guarantees for computing running
total this way from Microsoft. I can only recommend you not to use it.

The "normal" way to compute running total in SQL 2008 or earlier is to use a
subquery, but the execution time is proporptional to the square of number of
rows. In practice an iterative solution is better. You can also try to
encode the iteration in a recursive CTE. You can also look at Adam
Machanic's blog; he has been doing a lot of stuff in the CLR.

In SQL 2012 it is all a lot simpler, thanks to new SQL constructs.