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

• Jeff Moden

  SSC Guru

  Points: 1004582

  More actions

  October 6, 2012 at 9:41 am

  #1546426

  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

  September 10, 2013 at 7:37 am

  #1648882

  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.