'Update variable' technique for calculating running totals

Question

Hi,
Quick question regarding calculating running totals in T-SQL.

Robin Hames describes a technique here: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx that uses:

SET @RunningTotal = 0;
UPDATE t
SET @RunningTotal = RunningTotal = @RunningTotal + Sales;

However that cannot be trusted because it relies on the order of the data.

A commenter suggests that placing a clustered index on the data defining the order and then taking a table lock will guarantee the correct order but I'm skeptical that this is the case. MAXDOP may help but again, I'm skeptical.

Is there a way to make this technique 100% reliable.

thanks
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 1

Jamie,

No, that technique is not reliable.

Ordered UPDATE and Set-Based Solutions to Running Aggregates
http://www.sqlmag.com/Articles/ArticleID/102251/102251.html?Ad=1


AMB

Answer 2

Jamie,

No, that technique is not reliable.

Ordered UPDATE and Set-Based Solutions to Running Aggregates
http://www.sqlmag.com/Articles/ArticleID/102251/102251.html?Ad=1


AMB

Great stuff, good old Itzik.

Thanks Aaron.

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 3

Jamie,

No, that technique is not reliable.

Ordered UPDATE and Set-Based Solutions to Running Aggregates
http://www.sqlmag.com/Articles/ArticleID/102251/102251.html?Ad=1


AMB

Great stuff, good old Itzik.

Thanks Aaron.

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

I know you meant to say Alejandro.

Alejandro Mesa Balmori
AMB

Answer 4

Adam Machanic has shown some CLR code that is very slick for doing running totals too IIRC.

There is a HUGE thread with many types of solutions and LOTS of benchmarking on sqlservercentral.com.  VERY informative reading.

---

SQL Server MVP

Answer 5

See also http://forum.lessthandot.com/viewtopic.php?f=17&t=7601&p=38243&hilit=running+total#p38243 and links from it.

---

Premature optimization is the root of all evil in programming.

Donald Knuth, repeating C. A. R. Hoare

My blog

Answer 6

Jamie,

No, that technique is not reliable.

Ordered UPDATE and Set-Based Solutions to Running Aggregates
http://www.sqlmag.com/Articles/ArticleID/102251/102251.html?Ad=1


AMB

Great stuff, good old Itzik.

Thanks Aaron.

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

I know you meant to say Alejandro.

Alejandro Mesa Balmori
AMB

Whoops. Sorry Alejandro! :)

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 7

Jamie,

No, that technique is not reliable.

Ordered UPDATE and Set-Based Solutions to Running Aggregates
http://www.sqlmag.com/Articles/ArticleID/102251/102251.html?Ad=1


AMB

If you follow the rules for usage, it's 100% reliable and no one who has followed the rules for usage has been able to break it.

---

--Jeff Moden

Answer 8

Jamie,

No, that technique is not reliable.

Ordered UPDATE and Set-Based Solutions to Running Aggregates
http://www.sqlmag.com/Articles/ArticleID/102251/102251.html?Ad=1


AMB

Great stuff, good old Itzik.

Thanks Aaron.

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

BWAA-HAAAA!!  Yep... "good old Itzik".  But whether he calls it set base or not and whether it's guaranteed or not, even he hasn't been able to get it to break if the rules for usage are followed.  And guess what... even stuff that is well documented and supposedly "guaranteed to work" by Microsoft don't work... that's why we have hot fixes, CU's, and SP's. ;-)  Even WHERE column IS NULL failed in the face of parallelism back in SQL Server 2000 and they have another hot fix for a similar problem for 2008 right now.

---

--Jeff Moden

Answer 9

Hi,
Quick question regarding calculating running totals in T-SQL.

Robin Hames describes a technique here: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx that uses:

SET @RunningTotal = 0;

UPDATE t

SET @RunningTotal = RunningTotal = @RunningTotal + Sales;

However that cannot be trusted because it relies on the order of the data.

A commenter suggests that placing a clustered index on the data defining the order and then taking a table lock will guarantee the correct order but I'm skeptical that this is the case. MAXDOP may help but again, I'm skeptical.

Is there a way to make this technique 100% reliable.

thanks
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Yes... use both the things you just said and don't try it on anything that's partitioned.  Also throw in an update of a variable from the lead column in the clustered index and don't use any joins.  Once you get one working correctly, it won't fail.

Now if I can just keep the moderator's from censoring me.  :-)

---

--Jeff Moden