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
We can make a small code change which I believe guarantees that that particular quirk is impossible. If we modify one piece of your original code
SET @AccountRunningTotal = AccountRunningTotal =
CASE
WHEN AccountID = @PrevAccountID
THEN @AccountRunningTotal+Amount
ELSE Amount
END,
to be instead
SET @AccountRunningTotal = AccountRunningTotal =
CASE
WHEN Sequence = @Sequence+1
THEN CASE
WHEN AccountID = @PrevAccountID
THEN @AccountRunningTotal+Amount
ELSE Amount
END
ELSE 1/0
END,
then I think that the compound case statement which is the RHS of the assignment has to be evaluated at a single row - allowing the optimiser to split the evaluation of a single scalar expression so that parts of it are evaluated using elements from different rows would quite independently of quirky update render the assignment components of a SET clause so ill-determined as to be useless.
But of course the optimizer is a law unto itself, according to some, so I could be wrong.
edit: had an extra [/quote] in there which jumbled thiongs.
Tom