Paul White NZ (9/4/2010)

---

If we add the safety check, things work out nicely:
(...)
Correct results and an optimal query plan!

Jeff Moden (9/4/2010)

---

Hugo, it takes one awesome professional to post something like that above. Even if we vehemently disagree (and you'll be surprised that we disagree less than you think) on something, the tone of my future conversations with you will be markedly different. You're just trying to do the same thing I am... help people. Thank you for your courtesy and good will.

WayneS (9/5/2010)

---

Hugo, I have a small request. Instead of posting the page #, could you post the url of the post (in case you're not aware of this, click the post # at the bottom left of the post and up pops a message box with the complete url to the post).

The reason I ask is that the # of posts to display in a page is configurable. If yours is set to 10 per page, then your post on page 4 is between post 31-40. If yours is set to 20, then your post is between 61-80. Mine is set to 50 per page - which is between 151-200.

For me, page 11 would be for posts 550-600, and we're not that high yet.

Hugo Kornelis (9/5/2010)

---

I am sorry, Wayne. I never knew this is configurable. Thanks for teaching me something new. I believe that Paul has already helped you find the posts I was refering to? If not, let me know and I'll post a better link.

Just give it a few days...

WayneS (9/5/2010)

---

Not a problem. FYI, you can adjust this at http://www.sqlservercentral.com/Forums/EditForumSettings.aspx

.... I hope not! We're just broke 200!.

Hugo Kornelis (9/5/2010)

---

Indeed. The check itself changes the options for the optimizer. The method now even appears to be immune to parallel execution problems, since the parallel streams have to be gathered and re-synched before the row numbers can be calculated.

Hugo Kornelis (9/5/2010)

---

There may be a way to break this method on current versions of SQL Server, but it takes someone smarter then me to find it. And even if you accidentally stumble over it, or if new versions of the optimizer start to wreck this method, you're still safe because of the builtin safety check.

Paul White NZ (9/6/2010)

---

I can conceive of a way to break the safety check, but it requires significant effort from the imagination:

We would need the optimiser to produce a plan that separates the sequence check into a Compute Scalar separate from that which performs the quirky update variable assignments. Further, something like an explicit sort would be needed between those two Compute Scalars, arranged very particularly so that the rows are in sequence at the safety check, but not at the variable-assignment iterator. Such a plan is presently all but impossible (and that may be understating it) but even so...

Paul

SET     @AccountRunningTotal = AccountRunningTotal = 
            CASE 
                WHEN AccountID = @PrevAccountID 
                THEN @AccountRunningTotal+Amount 
                ELSE Amount 
            END,

SET     @AccountRunningTotal = AccountRunningTotal = 
            CASE 
                WHEN Sequence = @Sequence+1
                THEN CASE 
                           WHEN AccountID = @PrevAccountID 
                           THEN @AccountRunningTotal+Amount 
                           ELSE Amount 
                         END
                 ELSE 1/0
            END,