Quirky Update in SQL 2012

Question

Quirky Update in SQL 2012

karthik M

One Orange Chip

Points: 29139
More actions
October 6, 2012 at 3:22 am

#269022

All,
is the "Quirky Update" hole is still open in sql 2012 shop too? or is there any patch applied to close the hole?
if yes, what is the workaround for this? I remember Jeff mentioned in one of his interview this was closed. But i am not sure.
karthik

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 1

karthik M (10/6/2012)
All,
is the "Quirky Update" hole is still open in sql 2012 shop too? or is there any patch applied to close the hole?
if yes, what is the workaround for this? I remember Jeff mentioned in one of his interview this was closed. But i am not sure.

Wayne Sheffield wrote a great blog article on it including some performance testing.

http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/

The "new" method for doing running totals is still slower than the Quirky Update method but not by a whole lot. Better than that, the new method is supported by MicroSoft which helps people avoid that queazy feeling they get when they don't understand something.

--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)

Robb Melancon Ten Centuries Points: 1129 More actions · Answer 2

Although the new functionality may help with running total, it does not help when trying to roll through a table being able to update a row based on values from the previous row. In other words, anything not aggregate based. Using the TransactionDetail example if I needed to calculate the daily and running performance from one day to the next, I would need to use the formula (Amount - YesterdayAmount) / YesterdayAmount to calculate each rows daily performance. QuirkyUpdate is the only viable/speedy way that I have found to do this.