[ 9/8/10 Updated because of syntax error in sample code ]
How do you calculate a Running Total in SQL Server (using tSQL)?
There are many techniques to creating a running total using SQL. It was not until SQL 2005 that we had the option to use a CTE. A CTE (Common Table Expression) allows you to create a recursive operation. And what is as recursive as a running total function?
In the sample below, I am use three different running total techniques on a table I create for testing (cleverly called x). The first technique is an inner join, the second (and probably most common) is a sub select. The last uses a CTE.
I admit the CTE solution appears more complicated. Especially if you are not familiar with the CTE syntax. But when you see the performance delta you will find it worth your while.
I tested this on multiple machines because I didn’t believe it. But it’s true. And look at statistics, the CTE is so much better it is frightening. Here are the results for a running total against 10,000 rows. You might want to run it yourself to prove the results. Just copy/paste.
Performance Results
Inner Join = 75,390ms
returned 9,999 rows
Sub Select = 47,580ms
returned 9,999 rows
CTE = 793ms (you are reading that correctly!)
returned 9,999 rows
See the code here: http://codepaste.net/bfwqmv
I should give some credit to this post.