能否基于外键值创建分组自动递增的autoId列？

表结构 #

id            int IDENTITY(1,1)  -- 自增主键
foreignKeyId  int                -- 分组依据外键
autoId        int                -- 分组内自增字段，非唯一

需求 #

autoId需按foreignKeyId分组独立递增：

• 插入foreignKeyId=1的记录时，autoId从1开始依次+1
• 插入foreignKeyId=2的记录时，autoId重新从1开始计数

原触发器的问题 #

之前用触发器实现，但该方案在高并发插入时会出现重复autoId的问题，且大规模数据场景下性能较差。原触发器代码：

CREATE TRIGGER [dbo].[TableAutoIdUpdate]
ON [dbo].[Table]
AFTER INSERT
AS
BEGIN
    DECLARE @autoId int;
    
    SELECT @autoId = COALESCE(MAX(autoId), 0) + 1 
    FROM Table
    WHERE foreignKeyId IN (SELECT foreignKeyId FROM inserted);

    UPDATE Table 
    SET autoId = @autoId 
    WHERE id IN (SELECT id FROM inserted);
END

推荐解决方案 #

1. 用MERGE+存储过程实现原子操作 #

利用MERGE语句的原子性，确保分组内autoId的计算和插入是同一个事务，避免并发冲突。创建存储过程来处理插入：

CREATE PROCEDURE [dbo].[InsertRecordWithGroupAutoId]
    @foreignKeyId int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @newAutoId int;

    MERGE INTO dbo.[Table] AS Target
    USING (SELECT @foreignKeyId AS foreignKeyId) AS Source
    ON 1 = 0 -- 强制走INSERT分支
    WHEN NOT MATCHED THEN
        INSERT (foreignKeyId, autoId)
        VALUES (Source.foreignKeyId, 
                (SELECT COALESCE(MAX(autoId), 0) + 1 FROM dbo.[Table] WHERE foreignKeyId = Source.foreignKeyId))
    OUTPUT inserted.autoId INTO @newAutoId;

    SELECT @newAutoId AS GeneratedAutoId;
END

调用方式：

EXEC dbo.InsertRecordWithGroupAutoId @foreignKeyId = 1;

2. 优化触发器（兼容旧场景） #

如果必须保留触发器模式，可以通过提升事务隔离级别+行级锁定来解决并发问题，但会牺牲一定性能：

CREATE TRIGGER [dbo].[TableAutoIdUpdate]
ON [dbo].[Table]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    UPDATE t
    SET t.autoId = (SELECT COALESCE(MAX(autoId), 0) + 1 FROM dbo.[Table] WHERE foreignKeyId = i.foreignKeyId)
    FROM dbo.[Table] t
    JOIN inserted i ON t.id = i.id;
END

3. 序列方案（适用于固定分组） #

如果foreignKeyId是固定已知的，可以为每个分组创建独立序列，插入时直接调用序列取值：

-- 为foreignKeyId=1创建序列
CREATE SEQUENCE dbo.Seq_AutoId_1
START WITH 1
INCREMENT BY 1;

-- 插入时使用
INSERT INTO dbo.[Table] (foreignKeyId, autoId)
VALUES (1, NEXT VALUE FOR dbo.Seq_AutoId_1);

此方案仅适合分组固定的场景，动态新增分组时需手动创建序列，灵活性不足。

核心注意点 #

• 并发场景下，必须保证MAX(autoId)+1的计算与插入操作的原子性，否则会出现重复autoId
• 优先推荐MERGE+存储过程的方案，在并发和性能间取得较好平衡

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