Поведение SQL Server при увеличении переменной и обновлении столбца

Могу повторить проблему с ложным подсчетом в первой инструкции. Хоть и приятно знать, что вы можете назначить переменную в том же самом SET, но делать этого не следует.

Что касается второй инструкции, я считаю, что в T-SQL a = b = c не работает независимо от того, присваиваете ли вы или сравниваете.

Нет никакой гарантии, что какой-либо код будет работать правильно. Если тип данных InterfaceID будет превышен, вы столкнетесь с проблемами, и это только один сценарий.

Я попытался воспроизвести здесь общее требование:

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 

Мне просто пришло в голову, что вам не нужно дополнительно запрашивать таблицу, чтобы получить максимум. Этого можно добиться с помощью оконной функции.

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 

Ни один из этих вариантов не гарантированно сработает. Это известно как трюк «причудливого обновления», и он ужасно ненадежен и зависит от капризов оптимизатора.

В документации говорится:

Имена переменных можно использовать в операторах UPDATE, чтобы показать затронутые старые и новые значения, но это следует использовать только тогда, когда оператор UPDATE влияет на одну запись.

По сути, это та же проблема, что и трюк с «объединением переменных» с использованием SELECT @i +=, который также является документированным антишаблоном.

Факт остается фактом: как только вы используете переменную слева от UPDATE или SELECT и имеется несколько строк, все ставки отменяются и результаты становятся недетерминированными. Компилятор имеет полное право преобразовать то, что вы написали, в:

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

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

что, очевидно, не было намерением.

Правильный метод — тот, который отметил @DerU: используйте CTE с ROW_NUMBER(), чтобы назначить номера, начиная с максимального, а затем обновите CTE. Максимальное существующее значение должно иметь UPDLOCK, чтобы обеспечить правильную блокировку, и лучше всего, если у вас также есть индекс для этого столбца.

Единственная часть этих трюков, которая гарантирована, это то, что левая часть всегда принимает значение после UPDATE для этой строки, а правая часть всегда имеет значение перед этой строкой. Так что для одной строки все работает нормально. Но для нескольких строк не предоставляются никакие гарантии.