MSSQL如何创建带固定前缀自增序列的动态参考列？

DevOps 小满

2026-10-1

原代码错误原因

MSSQL 不存在concatenate字符串拼接函数，正确的拼接函数为CONCAT()，也可使用+运算符做字符串拼接
IDENTITY(1,1)是列属性，不能直接作为默认值表达式调用

最优实现方案（推荐）

用IDENTITY辅助列+持久化计算列实现，原生支持TRUNCATE表后自动重置序列，无需额外维护逻辑，并发场景下也能保证正确性：

-- 新增自增辅助列，可根据需要设置为隐藏列避免业务层感知
ALTER TABLE dbo.DPC_fact_MAIN
ADD REF_SEQ INT IDENTITY(1,1) NOT NULL;

-- 新增持久化计算列作为最终参考标识，会自动物理存储，支持加索引
ALTER TABLE dbo.DPC_fact_MAIN
ADD [REFERENCE] AS CONCAT('DPC#', REF_SEQ) PERSISTED NOT NULL;

特性说明

新增行时会自动生成DPC#1、DPC#2格式的参考标识，无需手动赋值
执行TRUNCATE TABLE dbo.DPC_fact_MAIN清空表时，IDENTITY序列会自动重置为1，新插入数据的标识会重新从DPC#1开始计数
对已有数据的表执行上述语句时，会自动为所有存量行填充对应的序列值，无需额外操作

替代方案（无辅助列场景）

如果不允许新增辅助列，可通过INSERT触发器实现，注意需要替换代码中的主键列为你的表实际主键：

-- 新增允许空的标识列
ALTER TABLE dbo.DPC_fact_MAIN
ADD [REFERENCE] NVARCHAR(10) NULL;
GO

-- 创建插入触发器自动赋值
CREATE OR ALTER TRIGGER TRG_DPC_fact_MAIN_GEN_REF
ON dbo.DPC_fact_MAIN
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @max_seq INT = ISNULL((SELECT MAX(TRY_CAST(REPLACE([REFERENCE],'DPC#','') AS INT)) FROM dbo.DPC_fact_MAIN WHERE [REFERENCE] IS NOT NULL),0);

    UPDATE m
    SET [REFERENCE] = CONCAT('DPC#', @max_seq + ROW_NUMBER() OVER(ORDER BY (SELECT 1)))
    FROM dbo.DPC_fact_MAIN m
    INNER JOIN inserted i ON m.YourPrimaryKey = i.YourPrimaryKey; -- 替换为实际主键列
END
GO