SQL 2005/8 Quirky update “black arts”

问题:

Hi all SQL Server Blackbelt masters out there.

I have a simple question that I need to solve. I am trying to do some quirky update on a table. Specific structure of the table is not important and my problem can essentially be reduced to the following one:

update SalesTotal
set @total = total = @total + sales,
@flag = flag = case when @flag = 1 then 0 
                    when @total > x then 1
                    else 0 end

The question is CAN I TRUST that @total variable will have a new value ie. will execute before the second set part, or will it contain an "old" value (from previous assignment) or it is undefined. I am pursuing this problem for some time now, and can't find a solution and it is really important for my update (I want to update a table in one go). One of the values I want to update depends on the value of the other (in the same row) so the only way for it to work in a single update is if it works.

Hope You can help me

回答1:

No, you cannot make any assumption of imperative order of execution. There is absolutely no guarantee about the order of the updates tacking place in the table, there is absolutely no guarantee that the @variable is updated once for each table SET, and the update may well run with a spool step for Halloween protection.