What is the best way to calculate a running total in SQL Server without using a cursor?

Question

ICSM Computer · Answer

The best way to calculate a running total in Microsoft SQL Server without using a cursor is by using window functions, specifically the SUM() function with the OVER() clause.

This approach is:

Faster than cursors
Easier to read
Optimized by SQL Server
Scalable for large datasets

Recommended Method: SUM() OVER()

Example Table

CREATE TABLE Sales (
    SaleID INT,
    SaleDate DATE,
    Amount DECIMAL(10,2)
)

Sample data:

SaleID	SaleDate	Amount
1	2026-01-01	100
2	2026-01-02	200
3	2026-01-03	150
4	2026-01-04	300

Running Total Query

SELECT
    SaleID,
    SaleDate,
    Amount,
    SUM(Amount) OVER (
        ORDER BY SaleDate
    ) AS RunningTotal
FROM Sales

Output

SaleID	SaleDate	Amount	RunningTotal
1	2026-01-01	100	100
2	2026-01-02	200	300
3	2026-01-03	150	450
4	2026-01-04	300	750

How It Works

The key part is:

SUM(Amount) OVER (ORDER BY SaleDate)

SQL Server:

Orders rows by SaleDate
Maintains a cumulative sum
Returns the running total for each row
This avoids row-by-row processing entirely.

Running Total by Group

You can also calculate running totals within categories using PARTITION BY.

Example

SELECT
    Department,
    EmployeeName,
    Salary,
    SUM(Salary) OVER (
        PARTITION BY Department
        ORDER BY EmployeeName
    ) AS DeptRunningTotal
FROM Employees

This resets the running total for each department.

Why Avoid Cursors?

Cursors process rows one at a time (RBAR: Row By Agonizing Row).

Problems with cursors:

Slow performance
High memory usage
Poor scalability
More complex code

Window functions are set-based and much more efficient.

Older Alternative (Before SQL Server 2012)

Before window functions improved, developers used correlated subqueries:

SELECT
    s1.SaleID,
    s1.Amount,
    (
        SELECT SUM(s2.Amount)
        FROM Sales s2
        WHERE s2.SaleID <= s1.SaleID
    ) AS RunningTotal
FROM Sales s1

This works, but it is much slower on large tables.

Performance Tips

Use Proper Indexes

For example:

CREATE INDEX IX_Sales_SaleDate
ON Sales(SaleDate)

This improves ordering performance.

Best Practice

For modern SQL Server versions:

SUM(column_name) OVER (ORDER BY column_name)

is the preferred and most efficient solution for running totals.

Conclusion

The best cursor-free approach for running totals in SQL Server is:

SUM() OVER() window function
Set-based processing
Better readability
Excellent performance

It’s the standard modern SQL technique for cumulative calculations.

forum

What is the best way to calculate a running total in SQL Server without using a cursor?

Can you answer this question?

1 Answers

Recommended Method: SUM() OVER()

Example Table

Running Total Query

Output

How It Works

Running Total by Group

Example

Why Avoid Cursors?

Older Alternative (Before SQL Server 2012)

Performance Tips

Use Proper Indexes

Best Practice

Conclusion