The fastest methods (QU, rCTE, Cursors) have been mentioned already. I recently tested this (following a lively discussion on this topic) and here is what I found:

The slower methods include:

-- A. COALESCE method (9sec for 5K records, 2:30 for 20K)

BEGIN

SELECT [Day],

Sales,

Sales+

COALESCE((SELECT SUM(Sales)

FROM ##Sales b

WHERE b.[Day] < a.[Day]),0) AS [Running Total]

FROM ##Sales a

ORDER BY [Day]

END

GO

-- B. Cross Join method (2sec for 5K records, 0:26 for 20K)

SELECT a.[Day],

a.Sales,

SUM(b.Sales) [Running Total]

FROM ##Sales a

CROSS JOIN ##Sales b

WHERE (b.[Day] <= a.[Day])

GROUP BY a.[Day],a.Sales

ORDER BY a.[Day],a.Sales

-- NOTE: B & C have the same query plan.

-- C. Self Join method; Equal to B (2sec for 5K records, 0:26 for 20K)

SELECT a.[Day],

a.Sales,

SUM(b.Sales) [Running Total]

FROM ##Sales a

JOIN ##Sales b

ON (b.[Day] <= a.[Day])

GROUP BY a.[Day],a.Sales

ORDER BY a.[Day],a.Sales

-- D. Cross Apply/Self-Join method (4 sec for 5K records, 1:09 for 20K)

SELECT[Day],

[Sales],

[Running Total]

FROM ##Sales a

CROSS APPLY

(

SELECT [Running Total] = sum(Sales)

FROM ##Sales

WHERE [Day] <= a.[Day]

) RT

ORDER BY [Day];

You are using the cross join method (B).

As you can see, these methods become painfully slow once you hit 20K records.

Now, looking at the aforementioned faster methods:

-- E. rCTE method - Very fast, (17 sec for 1M rows)

;WITH CTE ([Day], [Sales], [Running Total])

AS

(

SELECT[Day],

[Sales],

[Sales]

FROM ##Sales

WHERE [Day] = 1

UNION ALL

SELECTa.[Day],

a.[Sales],

CTE.[Running Total] + a.[Sales]

FROM CTE

JOIN ##Sales a ON CTE.[Day] + 1 = a.[Day]

)

SELECT * FROM CTE

OPTION (MAXRECURSION 0)

GO

-- F. "Quirky Update" Method (fastest, 12 sec for 1M rows)

DECLARE @PrevDay INT, @RunningTotal MONEY = 0

DECLARE @sales TABLE

(

[Day#]int,

[Sales#]MONEY,

[RunningTotal]MONEY,

PRIMARY KEY([Day#] ASC)

);

INSERT INTO @sales ([Day#],[Sales#])

SELECT * FROM ##Sales;

UPDATE @sales

SET @RunningTotal = RunningTotal =CASE

WHEN [Day] = @PrevDay

THEN @RunningTotal+[Sales#]

ELSE Sales#

END,

@PrevDay = [Day]

FROM ##Sales WITH (TABLOCKX)

OPTION (MAXDOP 1)

SELECT * FROM @sales

GO

-- G. Optimized Cursor, (44sec for 1M rows, 70 seconds without FAST_FORWARD)

BEGIN

DECLARE @Day int, @Sales money

DECLARE @RunningTotal money = 0

DECLARE @SalesTbl_2 TABLE

(

[Day]int,

Salesmoney,

RunningTotalmoney,

PRIMARY KEY([Day])

)

DECLARE rt_cursor CURSOR FAST_FORWARD FOR

SELECT [Day], Sales

FROM ##Sales

ORDER BY [Day]

OPEN rt_cursor

FETCH NEXT

FROM rt_cursor

INTO @Day,@Sales

WHILE @@FETCH_STATUS = 0

BEGIN

SET @RunningTotal = @RunningTotal + @Sales

INSERT @SalesTbl_2

VALUES (@Day,@Sales,@RunningTotal)

FETCH NEXT FROM rt_cursor INTO @Day,@Sales

END

CLOSE rt_cursor

DEALLOCATE rt_cursor

SELECT [Day],Sales,RunningTotal FROM @SalesTbl_2

END

GO

Each of these guys will blast through several hundred thousand rows in seconds. QU is still new to me and I'm playing around with it - it is usually the fastest. I personally prefer the rCTE because, though it's slighltly slower than the QU method, it's the most elegant and easiest to read (IMHO) of the faster solutions.

The other take-away from my testing is that cursors get a bad rap (and often rightfully so) but, in this case, a cursor is faster than all but a the QU and rCTE methods. It's also worth noting the performance difference using FAST_FORWARD with cursors when appropriate.