Я пытаюсь воспроизвести проблему, описанную в этом предыдущем С.О. вопрос, где цель:
По сути, «продолжите» последовательность.
Заявленное решение кажется хорошим: здесь используются имена таблиц исходного автора, имена столбцов и т.д.
DECLARE @i int = (SELECT ISNULL(MAX(interfaceID), 0) + 1 FROM prices)
UPDATE prices
SET interfaceID = @i, @i = @i + 1
WHERE interfaceID IS NULL
но было опубликовано еще в 2012 году и изменено в 2021 году. Я подозреваю, что за это время в SQL Server могло измениться что-то фундаментальное...
Я обнаружил, что моя собственная версия этого «начинает» последовательность на один номер слишком высоко, как будто добавочное присвоение @i = @i + 1
происходит ДО присвоения столбцу: SET interfaceID = @i
Я обнаружил, что небольшую модификацию этого можно заставить работать по назначению:
DECLARE @i int = (SELECT ISNULL(MAX(interfaceID), 0) FROM prices)
UPDATE prices
SET @i = interfaceID = @i + 1
WHERE interfaceID IS NULL
Здесь я не добавляю 1 к начальному @i, и присваивание является «составным» (это правильный термин?), например. @i = interfaceID = @i + 1
Для разработчика C# это имеет смысл (A становится равным возвращаемому значению присвоения B = C, следовательно, A = C).
«Гарантированно» ли какая-либо из этих двух конструкций выдает правильный результат? Я где-то читал, что для первого (два отдельных назначения) порядок выполнения не гарантируется в SQL Server, но в моем SQL Server (2016 SP2) он определенно сначала увеличивает @i
, а затем присваивает его столбцу.
Надеюсь, это понятно, и большое спасибо!
Опробовал оба показанных примера SQL, надеясь выяснить, гарантирован ли порядок присвоения.
Также нашел эту ссылку haacked.com, но она датирована 2004 годом — ожидаемое поведение другое. В 2020 году появился комментарий: «Эй, он начинается не с того номера!» что, по моему мнению, подтверждает, что где-то в истории SQL Server поведение изменилось.
Не тратьте на это время, подобные трюки не нужны со времен sql server 2000.
Могу повторить проблему с ложным подсчетом в первой инструкции. Хоть и приятно знать, что вы можете назначить переменную в том же самом 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
Скорректирован ответ, чтобы приблизиться к сценарию @clumsy.
Спасибо @DerU, как разработчик преимущественно C#, я понятия не имел, что структура с ROW_NUMBER и OVER..ORDER может использоваться таким образом - в частности, я не знал, что CTE также может быть предметом оператора UPDATE. . Я усвоил две бесценные вещи, и, изменив их под свою таблицу, они работают отлично.
SELECT+UPDATE в транзакции не предотвратит возможность коллизий... это связано с используемыми блокировками.
На самом деле я не совсем уверен, что это решение защищено от условий параллелизма; это будет зависеть от того, будет ли SQL Server достаточно умен, чтобы с самого начала принять правильные блокировки.
@DaleK Требуется UPDLOCK
для правильной блокировки и в идеале индекс v
Ни один из этих вариантов не гарантированно сработает. Это известно как трюк «причудливого обновления», и он ужасно ненадежен и зависит от капризов оптимизатора.
В документации говорится:
Имена переменных можно использовать в операторах 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
для этой строки, а правая часть всегда имеет значение перед этой строкой. Так что для одной строки все работает нормально. Но для нескольких строк не предоставляются никакие гарантии.
есть над чем подумать
Зачем использовать этот дрянной метод и не использовать
ROW_NUMBER
?