SQL Server behaviour when both incrementing a variable AND updating a column

I'm attempting to replicate the issue described in this previous S.O. question, where the goal is to:

find the maximum NON-NULL value in a column
update all the NULL (missing) values for that column with an incrementing sequence, starting 1 higher than the found maximum.

In effect, "continue" the sequence.

The stated solution appears good: here using the original poster's table names, column names etc...

DECLARE @i int  = (SELECT ISNULL(MAX(interfaceID), 0) + 1 FROM prices)

UPDATE prices
SET interfaceID = @i, @i = @i + 1
WHERE interfaceID IS NULL

but was posted back in 2012, modified in 2021. I'm suspecting that something fundamental may have changed in SQL Server in the intervening time...

I'm finding that my own version of this "starts" the sequence one number too high, as if the incremental assignment @i = @i + 1 is occurring BEFORE assigning to the column: SET interfaceID = @i

I found a slight modification of this could be made to work as intended:

DECLARE @i int  = (SELECT ISNULL(MAX(interfaceID), 0) FROM prices)

UPDATE prices
SET @i = interfaceID  = @i + 1
WHERE interfaceID IS NULL

Here I am not adding 1 to the initial @i, and the assignment is "compound" (is that the right term?) e.g. @i = interfaceID = @i + 1 As a C# developer this makes sense (A becomes equal to the return value of assigning B = C, therefore A = C).

Are either of these two constructs "guaranteed" to produce the correct output? I've read somewhere that the first one (two separate assignments), the order of execution is not guaranteed in SQL Server, but in my SQL Server (2016 SP2) it certainly appears to increment @i first, then assign to the column.

Hope this is clear, and many thanks!

Tried out both the shown SQL samples, with an expectation of discovering whether order-of-assignment was guaranteed.

Also found this link haacked.com but it dates from 2004 - the expected behaviour is different. In 2020 there's a comment saying "hey, it doesn't start at the right number!" which I believe reinforces that behaviour has changed somewhere in SQL Server's history.

Solution

I can replicate the issue with the false counting in the first instruction. While cool to know, that you can assign the variable in the same SET, you still shouldn't.

About the second instruction, I believe in T-SQL a = b = c doesn't work, whether you assign or compare.

There is no guarantee for any code to run correctly. If the data type of interfaceID is exceeded, you will run into issues, to name just one scenario.

I tried to replicate the general requirement here:

DROP TABLE IF EXISTS #t
CREATE TABLE #T(v INT)
INSERT INTO #t(v) VALUES
 (NULL)
,(1)
,(NULL)
,(NULL)
,(2)
,(3)
,(NULL)
,(NULL)

SELECT v FROM #t

;WITH t AS(
SELECT 
  v
  ,v2 = ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) + (SELECT MAX(V) FROM #t WITH (UPDLOCK)) 
FROM #t
WHERE v IS NULL
)
UPDATE t
SET v = v2

SELECT v FROM #t

It just came to my mind that You don't need to query the table extra to get the max. This can be achieved with a window function.

DROP TABLE IF EXISTS #t
CREATE TABLE #T(v INT)
INSERT INTO #t(v) VALUES
 (NULL)
,(1)
,(NULL)
,(NULL)
,(2)
,(3)
,(NULL)
,(NULL)

SELECT v FROM #t

;WITH t AS(
SELECT 
  v
  ,v2 = ROW_NUMBER()OVER(ORDER BY v) + MAX(v)OVER() 
FROM #t
)
UPDATE t
SET v = v2
WHERE v IS NULL

SELECT v FROM #t