SQL Server变量递增与列更新的行为及写法可靠性验证

问题背景 #

需求是为prices表中interfaceID列的所有NULL值，分配从该列最大非NULL值+1开始的连续递增序列。

两种写法的正确性分析 #

1. 原写法（不可靠） #

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

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

这种写法的核心问题是：SQL Server不保证SET子句中多个独立赋值的执行顺序。尽管早期版本可能按从左到右的顺序执行，但查询优化器有权根据执行计划调整赋值顺序。你在SQL Server 2016 SP2中遇到的起始值偏高问题，就是因为优化器优先执行了@i = @i +1，再给interfaceID赋值，导致第一个NULL值拿到的是MAX+2而非预期的MAX+1。这种写法依赖未定义的实现细节，无法保证在所有版本或执行计划下都正确。

2. 修改后的写法（可靠） #

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

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

这种复合赋值（链式赋值）的写法是SQL Server中有明确行为保证的实现。SQL Server会按从右到左的顺序执行链式赋值：

• 首先计算@i +1（初始@i为MAX值或0，第一次计算结果为MAX+1）
• 随后将该值同时赋值给interfaceID和@i
• 后续循环中，@i已更新为上一次的赋值结果，因此下一个NULL值会拿到@i+1，实现连续递增

这种写法依赖SQL Server对复合赋值的定义行为，不会因版本升级或执行计划变化出现异常，是稳定可靠的解决方案。

总结 #

• 原写法依赖未定义的执行顺序，无法保证正确性，属于不推荐的写法
• 修改后的复合赋值写法符合SQL Server规范行为，能稳定实现需求

内容的提问来源于stack exchange，提问作者clumsybiker