SQL Server 2016 UPDATE statement - are variable assignments always processed before column updates?

Question

One of our application devs came to me with an interesting question. In the following code, you'll see that the update statement is assigning the current column values to two variables, but also updating those same columns to new values.

The question was, is it guaranteed that the variables @oStart and @oFinish will be assigned to the old values of [Start] and [Finish] and not the new values? Under testing, this has been the case, however, I'd like to confirm this is the guaranteed behaviour.

I expect the answer is "no - it's not guaranteed", and ultimately, it's down to the decision the optimiser takes. Regardless, this kind of ambiguous code is to be discouraged anyway (there are more elegant and readable ways of writing it), but would be interested to hear everyone's thoughts.

-- Parameters
DECLARE @StartDate smalldatetime = '2022-01-01',
       @EndDate smalldatetime = '2022-12-31';

-- Body
DECLARE @oStart smalldatetime,
       @oFinish smalldatetime;

UPDATE  [dbo].[MyTable]
SET     @oStart = [Start],
       @oFinish = [Finish],
       [Start] = @StartDate,
       [Finish] = @EndDate
WHERE   [ID] = 12;

Answer 1

Yes this is documented. See the documentation for @variable in the UPDATE syntax.

SET @variable = column ... sets the variable to the pre-update value of the column.

(As contrasted in the documentation to SET @variable = column = expression which will set the variable to the value after the column = expression assignment).

DECLARE @oldStart  SMALLDATETIME,
        @oldFinish SMALLDATETIME,
        @newStart  SMALLDATETIME,
        @newFinish SMALLDATETIME;

DECLARE @MyTable TABLE
  (
     ID     INT PRIMARY KEY,
     Start  SMALLDATETIME,
     Finish SMALLDATETIME
  )

INSERT @MyTable
VALUES(12, '1900-01-01', '1900-01-31');

UPDATE @MyTable
SET    @oldStart = [Start],
       @oldFinish = [Finish],
       @newStart = [Start] = '2022-01-01',
       @newFinish = [Finish] = '2022-12-31'
WHERE  [ID] = 12;

SELECT @oldStart  AS [@oldStart],
       @oldFinish AS [@oldFinish],
       @newStart  AS [@newStart],
       @newFinish AS [@newFinish]; 

Returns

@oldStart	@oldFinish	@newStart	@newFinish
1900-01-01 00:00:00	1900-01-31 00:00:00	2022-01-01 00:00:00	2022-12-31 00:00:00

Assuming ID is the primary key so the UPDATE is guaranteed to affect at most one row then personally I think this syntax is fine if you need to assign that value to a SQL variable.

It beats having to use the OUTPUT ... INTO clause to store these values somewhere and then a subsequent SELECT from that to assign to the variable.

Answer 2

The SQL standard stipulates that

The <update source> of each <set clause> contained in SCL [Set Clause List] is effectively evaluated for each row of [table] T before any row of T is updated

(emphasis mine). In your case it means that, according to the standard, @oStart and @oFinish must have the values of [Start] and [Finish], respectively, as they exist before the update.

Now, whether SQL Server complies with the standard in this regard is a different question.