如何在SQL Server中设置带年份前缀的分段自增标识列

现有代码的问题 #

你的代码存在几个关键问题：

1. 语法错误：计算列表达式缺少连字符，括号未闭合，且right('000' + ...)的补位长度不足（你需要6位序列，应该用'000000'）。
2. 逻辑错误：使用GETDATE()会导致每次查询时年份都取当前日期，插入时的旧数据年份会被动态更新，不符合需求。
3. 序列无法重置：IDENTITY列是全局自增逻辑，无法在跨年时自动将序列重置为000001。

正确实现方案 #

要实现「年份后两位-6位当年自增序列」且跨年自动重置的需求，推荐两种可靠方案：

方案1：使用序列（SEQUENCE）+ 默认约束（SQL Server 2012+） #

序列支持手动/自动重置，是实现跨年序列的最优选择：

1. 创建按年重置的序列

CREATE SEQUENCE ContactSeq
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999
    CYCLE -- 可选：当年份内序列到999999后循环，不需要可删除
    CACHE 10; -- 缓存提升插入性能，可按需调整

2. 创建目标表

存储插入时的固定年份，通过计算列生成最终格式的ID：

CREATE TABLE contacts (
    -- 存储插入时的年份后两位（持久化，避免后续变动）
    InsertYear AS RIGHT(YEAR(GETDATE()), 2) PERSISTED,
    -- 自动获取当年序列值
    SeqNum INT NOT NULL DEFAULT NEXT VALUE FOR ContactSeq,
    -- 生成目标格式的计算列，作为对外展示的ID
    Contact_ID AS CONCAT(InsertYear, '-', RIGHT('000000' + CAST(SeqNum AS VARCHAR(6)), 6)) PERSISTED,
    Contact_name VARCHAR(20),
    -- 组合主键保证年份+序列的唯一性
    PRIMARY KEY (InsertYear, SeqNum)
);

3. 跨年重置序列

每年年初执行以下语句重置序列（可通过SQL Server代理作业自动执行）：

ALTER SEQUENCE ContactSeq RESTART WITH 1;

方案2：使用触发器（兼容低版本SQL Server） #

如果无法使用序列，可通过触发器自动计算当年序列值：

1. 创建目标表

CREATE TABLE contacts (
    InsertYear CHAR(2) NOT NULL,
    SeqNum INT NOT NULL,
    Contact_ID AS CONCAT(InsertYear, '-', RIGHT('000000' + CAST(SeqNum AS VARCHAR(6)), 6)) PERSISTED,
    Contact_name VARCHAR(20),
    PRIMARY KEY (InsertYear, SeqNum)
);

2. 创建插入触发器

CREATE TRIGGER trg_Contacts_Insert
ON contacts
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @currentYear CHAR(2) = RIGHT(YEAR(GETDATE()), 2);
    DECLARE @nextSeq INT;

    -- 获取当年已有的最大序列值，无数据则从1开始
    SELECT @nextSeq = ISNULL(MAX(SeqNum), 0) + 1
    FROM contacts
    WHERE InsertYear = @currentYear;

    INSERT INTO contacts (InsertYear, SeqNum, Contact_name)
    SELECT @currentYear, @nextSeq, Contact_name
    FROM inserted;
END;

测试插入 #

插入数据时无需手动指定ID相关字段，系统会自动生成格式正确的自增ID：

INSERT INTO contacts (Contact_name) VALUES ('张三'), ('李四');

查询结果会得到类似24-000001、24-000002的ID，跨年插入时自动切换为25-000001。

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