• There are many so-called "set-based" solutions for Running Totals. One of the most common can be found in the following article which explains why it shouldn't be used.

    http://www.sqlservercentral.com/articles/T-SQL/61539/

    Other than the new windowing functionality for SUM offered in SQL Server 2012, all "set based" methods except the "Quirky Update" method and the multi-pass UPDATE method will usually get worse at an exponential rate as rowcounts increase. The multi-pass UPDATE method can also be worse than a cursor if the data doesn't have "groupings" in it. The "Quirky Update" method will beat even the new windowing functions in SQL Server 2012 but it's an undocumented feature that many fear to use. One of the problems with it is that it's not a single query. It requires some careful planning with variables and indexes and a check must be built in to make sure it reports an error if it "goes haywire".

    Since you have so few rows, my recommendation would be to avoid all of that and write a nice "firehose" (forward only, read only, static) cursor to solve your running total problem. In SQL versions less than 2012, it's the second fastest, second least resource intensive method there is.

    If you end up with a million rows, post back and I'll show you how to do the Quirky Update properly.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)