How to Calculate Running Totals in SQL Server

Guy-Glantser, 2017-12-01 (first published: 2017-11-19)

 

In show #84 of the SQL Server Radio Hebrew Edition, Matan and I talked about how to calculate running totals in SQL Server. I promised to upload a script with a complete demo of all the possible methods. So here it is.

 

Running Totals in SQL Server

 

The script first creates the “RunningTotals” database. Notice that you first need to create the “C:\RunningTotals” folder, because this is where SQL Server will try to store the database files. Alternatively, you can modify the CREATE DATABASE statement in the script to store the files in a different folder. Then the script creates the “Billing.Transactions” table, and populates it with test data. The table includes the “Balance” column, which is set to NULL before each method is being used. The goal is to populate the “Balance” column with the balance in each account after each transaction.

 

Here are the methods that I demonstrate in the script in descending order of execution time:

 

 1. Cursor with an UPDATE statement in each iteration

 2. Cursor with a temporary table and an INSERT statement in each iteration

 3. Correlated sub-query

 4. Window Function – SUM () OVER…

 5. In-place UPDATE with ORDER BY

 6. In-place UPDATE with an index hint

 

The nice thing about this script is that the execution time of each method is better than the previous one. We start with several minutes for the first method, and finish with just 7 seconds (on my laptop) to calculate the running totals for the entire table (one million transactions).

 

It’s very important to note that you can’t trust the two last methods (in-place update). In order to verify that these methods are reliable, we need to verify that we execute the update in an ordered fashion.

 

Method #5 uses a sub-query with a TOP 100 PERCENT and an ORDER BY in order to achieve that. This method is not supported by SQL Server (it compiles and runs successfully, but there is no guarantee regarding the order of rows). In recent versions of SQL Server, the optimizer completely ignores it.

 

Method #6 handles the sort order by specifying an index hint with the clustered index name. Again, it compiles and runs, but it doesn’t guarantee the sort order of the query.

 

The reason the two methods almost always produce the correct results is because the clustered index is already sorted in the desired way, and the storage engine simply scans the clustered index form left to right. It always works in the demo, because we have just created the index. In a real-life scenario, where there is fragmentation in the index, and pages are moved around, these methods might produce incorrect results.

 

Also, these two methods are not ANSI-SQL compliant. ANSI-SQL does not support the syntax of updating a column and a local variable in the same statement.

 

So the best solution, which is also reliable and supported, is method #4 – using the SUM aggregate function as a window function.

 

 

The post How to Calculate Running Totals in SQL Server appeared first on Madeira Data Solutions.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads