如何创建依赖客户列的标识列？实现订单号按客户自动递增

现有dbo.Orders表结构如下：

dbo.Orders (
    customerNumber INT,
    orderNumber INT,
    cost FLOAT, -- 仅为示例，表中还有其他列
    ...
)

需求是订单号（orderNumber）按客户（customerNumber）单独自动递增，插入示例如下：

INSERT INTO
    dbo.Orders (customerNumber, cost)
VALUES
    (1, 2500), -- 自动分配orderNumber: 1 
    (1, 3500), -- 自动分配orderNumber: 2
    (1, 1500), -- 自动分配orderNumber: 3
    (2, 650), -- 自动分配orderNumber: 1（客户2的首单）
    (2, 50), -- 自动分配orderNumber: 2
    (1, 100), -- 自动分配orderNumber: 4（客户1上一次订单号是3）

要求保证customerNumber与orderNumber的组合唯一，已通过UNIQUE KEY约束实现，但无法找到双列IDENTITY的实现方式；尝试过创建序列，但无法为每个客户单独配置序列。

自行实现的方案是先查询客户最新订单号再加1插入，代码如下：

SELECT @lastOrderNumber = orderNumber 
FROM dbo.Orders
WHERE customerNumber = @Customer

SET @newOrderNumber = ISNULL(@lastOrderNumber,0) + 1;

INSERT INTO dbo.Orders (customerNumber, cost, orderNumber)
VALUES (@Customer, 2500, @newOrderNumber)

但该方案存在以下问题：

• 操作繁琐，批量插入不便
• 并发插入时存在竞态条件，而IDENTITY可避免此类问题

现寻求更优实现方案。

方案1：使用触发器（推荐） #

通过INSTEAD OF INSERT触发器自动计算并分配订单号，同时利用事务锁机制避免并发冲突，且支持批量插入。

实现步骤： #

1. 先添加组合唯一约束兜底：

ALTER TABLE dbo.Orders ADD CONSTRAINT UQ_Orders_CustomerOrder UNIQUE (customerNumber, orderNumber);

2. 创建触发器：

CREATE TRIGGER trg_Orders_AssignOrderNumber
ON dbo.Orders
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 为插入的每一行计算对应客户的递增订单号
    INSERT INTO dbo.Orders (customerNumber, cost, orderNumber)
    SELECT
        i.customerNumber,
        i.cost,
        -- 获取客户已有最大订单号，批量插入时按行分配递增序号
        ISNULL(MAX(o.orderNumber), 0) + ROW_NUMBER() OVER (PARTITION BY i.customerNumber ORDER BY (SELECT NULL))
    FROM inserted i
    LEFT JOIN dbo.Orders o ON i.customerNumber = o.customerNumber
    GROUP BY i.customerNumber, i.cost; -- 这里需要包含表中所有要插入的其他列
END;

优势： #

• 插入时无需手动指定orderNumber，完全自动分配
• 天然支持批量插入，同一客户的多条插入会按顺序分配连续订单号
• 触发器在事务内执行，会锁定相关资源，彻底避免并发竞态问题

方案2：使用客户计数器表+存储过程 #

通过辅助表跟踪每个客户的订单号计数器，用存储过程安全获取下一个订单号，适合需要手动控制订单号生成逻辑的场景。

实现步骤： #

1. 创建客户订单号计数器表：

CREATE TABLE dbo.CustomerOrderCounters (
    customerNumber INT PRIMARY KEY,
    currentOrderNumber INT DEFAULT 0
);

2. 创建获取下一个订单号的存储过程：

CREATE PROCEDURE dbo.GetNextOrderNumber
    @customerNumber INT,
    @nextOrderNumber INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 使用UPDLOCK+HOLDLOCK锁避免并发冲突，原子性更新计数器
    UPDATE dbo.CustomerOrderCounters
    SET currentOrderNumber = currentOrderNumber + 1,
        @nextOrderNumber = currentOrderNumber + 1
    WHERE customerNumber = @customerNumber;

    -- 若客户是首次下单，初始化计数器
    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO dbo.CustomerOrderCounters (customerNumber, currentOrderNumber)
        VALUES (@customerNumber, 1);
        SET @nextOrderNumber = 1;
    END;
END;

3. 插入数据时调用存储过程：

DECLARE @nextOrderNum INT;
EXEC dbo.GetNextOrderNumber @customerNumber = 1, @nextOrderNumber = @nextOrderNum OUTPUT;

INSERT INTO dbo.Orders (customerNumber, cost, orderNumber)
VALUES (1, 2500, @nextOrderNum);

优势： #

• 逻辑清晰，可单独维护客户的订单号计数器
• 锁机制确保并发场景下不会出现重复订单号
• 可扩展支持批量获取订单号的需求

方案3：窗口函数批量插入 #

针对一次性批量导入大量数据的场景，直接用窗口函数计算订单号，无需依赖触发器或存储过程。

实现代码： #

-- 假设inserted_data是批量数据源（可以是临时表或其他查询结果）
INSERT INTO dbo.Orders (customerNumber, cost, orderNumber)
SELECT
    customerNumber,
    cost,
    ISNULL(prev_max_order, 0) + ROW_NUMBER() OVER (PARTITION BY customerNumber ORDER BY (SELECT NULL))
FROM (
    SELECT
        i.customerNumber,
        i.cost,
        MAX(o.orderNumber) OVER (PARTITION BY i.customerNumber) AS prev_max_order
    FROM inserted_data i
    LEFT JOIN dbo.Orders o ON i.customerNumber = o.customerNumber
) t;

优势： #

• 适合大数据量批量导入，执行效率高
• 无需额外对象（触发器/存储过程），临时场景快速实现

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