SQL Server序列在CASE语句中使用时出现值跳变问题求助

问题原因 #

你当前的代码逻辑是提前获取序列值存入变量，无论后续是否需要将这个值插入到表中，序列的计数器都会被推进。也就是说，哪怕StartDate为空，NEXT VALUE FOR Id_Sequence已经被执行，序列值已经被消耗，后续插入非空StartDate时自然会跳过这些被提前用掉的数值。

解决方案 #

方案1：直接在CASE语句中调用序列（推荐）

SQL Server 2016支持在INSERT的VALUES子句中直接使用NEXT VALUE FOR，无需提前存入变量。这样只有当StartDate非空时才会触发序列递增：

DECLARE @StartDate date
SET @StartDate = '20220816'

INSERT INTO dbo.table (StartDate, Id) 
VALUES (
    @StartDate,
    CASE WHEN @StartDate IS NOT NULL THEN NEXT VALUE FOR Id_Sequence ELSE NULL END
);

方案2：分支判断插入

如果因特殊限制无法直接在CASE中使用序列，可以通过IF...ELSE分支分开处理，仅在需要插入Id时获取序列值：

DECLARE @StartDate date
SET @StartDate = '20220816'

IF @StartDate IS NOT NULL
BEGIN
    INSERT INTO dbo.table (StartDate, Id)
    VALUES (@StartDate, NEXT VALUE FOR Id_Sequence)
END
ELSE
BEGIN
    INSERT INTO dbo.table (StartDate, Id)
    VALUES (@StartDate, NULL)
END

序列创建的优化建议 #

你原代码中使用ISNUMERIC判断Id是否为数字存在局限性（比如会误判'123.'这类格式为有效数字），建议改用TRY_CAST来确保Id能正确转换为整数：

DECLARE @IdMax INT;
SELECT @IdMax = ISNULL(MAX(TRY_CAST(Id AS INT)), 0) + 1 
FROM [db].[dbo].[table] 
WHERE TRY_CAST(Id AS INT) IS NOT NULL;

EXEC('CREATE SEQUENCE Id_Sequence
START WITH ' + CAST(@IdMax AS VARCHAR(10)) + '
INCREMENT BY 1
CACHE 1000;')

效果验证 #

使用上述修改后的代码，当StartDate为空时不会消耗序列值，后续非空场景下的Id会连续递增，不会出现跳过数值的情况。

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