Jeff Moden (11/10/2009)
Alexander Kuznetsov-291390 (11/10/2009)
These are incredibly smart techniques, but after playing with such approaches I decided that I don't want to put it into production - they are just a little bit too complex and they do not look robust to me. In my book this is one of those cases where denormalization allows for much faster and significantly simpler solutions:BTW, Adam Machanic recommends CLR cursors in such cases...
First, thanks for taking the time to stop by and post your thoughts, Alexander. I really appreciate it.
I'm not sure why you find the "Quirky Update" a bit too complex nor why you don't think the look robust, but that's OK. Your good method will nicely support ongoing running balance updates on insertion of new data, albeit, in a RBAR manner. I'll add some performance testing using your good method to my list of things to do.
To be sure, your method is very clever... I like it. I just think it will be a bit slow because of it's RBAR nature.
Jeff, I am not sure how RBAR is relevant to denormalization, yet when the running totals are stored right in your row, you don't need any joins, any cursors, anything - selects are as fast as it goes. For inserts, you retrieve only one previous row, so your inserts do not slow down as your table grows...