复合主键与自增INT列问题：插入数据时自增持续递增的疑问

这完全符合SQL Server中**自增列（Identity）**的预期行为，咱们先搞懂原因，再给你解决办法：

为什么当前现象是正常的？ #

Identity列的核心设计逻辑是全局独立递增——它只负责给每一条新插入的记录分配一个唯一的、连续（或近似连续）的数值，和表中的其他列没有任何关联。不管你插入的nvarchar列值重复多少次，只要执行了插入操作（哪怕插入失败回滚，某些场景下自增号还会跳号），Identity值都会自动+1。它的作用是给每行一个唯一标识，而不是配合其他列生成分组内的序号。

所以你四次插入不同/重复的nvarchar值，自增列从1到4依次递增，是完全符合它的设计预期的。

如何实现你要的复合主键逻辑？ #

你的需求应该是：针对每个nvarchar值，对应的INT列是从1开始递增的分组序号，同时(INT列, nvarchar列)作为复合主键保证唯一性。这种场景下Identity列无法满足，你可以用以下两种方案：

方案1：触发器自动生成分组序号（推荐） #

先去掉原表的Identity属性，然后用触发器在插入时自动计算当前分组的最大序号，再加1插入：

-- 移除Identity属性（如果原表已设置）
ALTER TABLE YourTableName ALTER COLUMN IntColumn INT NOT NULL;

-- 创建插入触发器
CREATE TRIGGER trg_GenerateGroupSeq
ON YourTableName
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 针对每个插入的Varchar值，计算对应的下一个序号
    INSERT INTO YourTableName (IntColumn, VarcharColumn)
    SELECT
        COALESCE(MAX(t.IntColumn), 0) + 1,
        i.VarcharColumn
    FROM inserted i
    LEFT JOIN YourTableName t ON t.VarcharColumn = i.VarcharColumn
    GROUP BY i.VarcharColumn;
END;

这样插入数据时，触发器会自动处理：

INSERT INTO YourTableName (VarcharColumn) VALUES ('FIRSTENTRY'); -- Int列自动填1
INSERT INTO YourTableName (VarcharColumn) VALUES ('FIRSTENTRY'); -- Int列自动填2
INSERT INTO YourTableName (VarcharColumn) VALUES ('SECONDENTRY'); -- Int列自动填1
INSERT INTO YourTableName (VarcharColumn) VALUES ('SECONDENTRY'); -- Int列自动填2

最终得到你想要的复合主键数据：

方案2：手动计算序号插入（适合少量插入场景） #

如果不想用触发器，可以在插入时手动计算当前分组的最大序号，注意要处理并发问题：

-- 单条插入（带并发锁）
BEGIN TRANSACTION;
DECLARE @NextSeq INT;
-- 使用UPDLOCK和HOLDLOCK防止并发插入时的主键冲突
SELECT @NextSeq = COALESCE(MAX(IntColumn), 0) + 1 
FROM YourTableName WITH (UPDLOCK, HOLDLOCK) 
WHERE VarcharColumn = 'FIRSTENTRY';

INSERT INTO YourTableName (IntColumn, VarcharColumn) VALUES (@NextSeq, 'FIRSTENTRY');
COMMIT TRANSACTION;

额外注意事项 #

1. 一定要保留(IntColumn, VarcharColumn)的复合主键，确保每个分组内的序号唯一。
2. 建议给VarcharColumn建立非聚集索引，提升触发器/手动查询时的分组最大值查询效率，尤其是数据量大的时候。
3. 触发器方案支持批量插入，比如一次插入多条不同/相同的Varchar值，都会自动计算对应的序号。

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