I must admit I was wrong on this one....

USE tempdb

GO

CREATE TABLE dbo.Accounts

(

ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,

TransactionDate datetime,

Balance float

)

go

DECLARE @i as INT

set @i = 0

while @i < 1000000

begin

insert into dbo.Accounts (TransactionDate, Balance) SELECT DATEADD(DD, @i, GETDATE()), CEILING(RAND() * 999)

SET @i = @i + 1

end

--SELECT * FROM dbo.Accounts

go

--activate execution plans before running this one

go

SELECT Accounts.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance,

( SELECT Sum( Balance )

FROM ( SELECT Top 5 A.Balance

FROM Accounts A

WHERE A.ID <= Accounts.ID

ORDER BY ID DESC

) AS A

) AS RunningTotal

FROM dbo.Accounts

go

DROP TABLE dbo.Accounts

That 1M rows query only processes 95MB of data (from stats in actual plan). I was expecting some sort of triangular join here, but the speed is really not all that bad. My statement returns the 1M rows in les than 8 sec (download included)... so that version might very well make it to production.

Thanks pp.pragnesh