"Quirky Update" Where Clause Problem

• iracildols

  SSC Journeyman

  Points: 95

  More actions

  July 8, 2015 at 10:39 am

  #302216

  Hi Jeff,

  Congratulations for the nice article and thanks for sharing your fantastic "Quirky Update". Very fast!

  With SQL Server 2012, I run the scripts in your article until Figure 18 without a problem. But when I run the script in Figure 19, it fails. Nothing is updated in the table TransactionDetail. Then, casually, I commented the WHERE CLAUSE and it worked. See below:

  --CODE FROM FIGURE 19 - The "Quirky Update" Running Total and Running Count

  /*************************************************************************************

  Pseduo-cursor update using the "Quirky Update" to calculate both Running Totals and

  a Running Count that start over for each AccountID.

  Takes 24 seconds with the INDEX(0) hint and 6 seconds without it on my box.

  *************************************************************************************/

  --===== Supress the auto-display of rowcounts for speed an appearance

  SET NOCOUNT ON

  --===== Declare the working variables

  DECLARE @PrevAccountID INT

  DECLARE @AccountRunningTotal MONEY

  DECLARE @AccountRunningCount INT

  --===== Update the running total and running count for this row using the "Quirky

  -- Update" and a "Pseudo-cursor". The order of the UPDATE is controlled by the

  -- order of the clustered index.

  UPDATE dbo.TransactionDetail

  SET @AccountRunningTotal = AccountRunningTotal = CASE

  WHEN AccountID = @PrevAccountID

  THEN @AccountRunningTotal + Amount

  ELSE Amount

  END,

  @AccountRunningCount = AccountRunningCount = CASE

  WHEN AccountID = @PrevAccountID

  THEN @AccountRunningCount + 1

  ELSE 1

  END,

  @PrevAccountID = AccountID

  FROM dbo.TransactionDetail WITH (TABLOCKX)

  --Where accountID = 1

  OPTION (MAXDOP 1)

  GO

  With the commented WHERE CLAUSE, the script runs in my machine in less then 1 sec.

  This problem happens with SQL Server 2014 also.

  Regards

  Iracildo