sql - 生成对于外键给定值唯一的增量ID

Question

我们正在开发一个电子商务系统，在该系统中，我们汇总了来自不同卖方的订单。您可以轻松想象，我们有一个Order表，其中包含所有卖方的订单数据。每个卖方都有一个唯一的AccountID，它是Order表中的外键。

我们要为进入系统的每个订单生成一个订单号，以便对于给定的卖家(和给定的AccountID)，这些订单号正在创建一个序列(第一个订单依次为1、2、3等)。

我们已经尝试了几种解决方案，但是它们有我们要避免的缺点。所有这些都在触发器内:

ALTER TRIGGER [dbo].[Trigger_Order_UpdateAccountOrderNumber] 
   ON [dbo].[Order]
   AFTER INSERT
BEGIN
     ...
END

我们的解决方案1是:

UPDATE
    [Order]
SET
    AccountOrderNumber = o.AccountOrderNumber
FROM
(
    SELECT
        OrderID,
        AccountOrderNumber =
            ISNULL((SELECT TOP 1 AccountOrderNumber FROM [Order] WHERE AccountID = i.AccountID ORDER BY AccountOrderNumber DESC), 1) +
            (ROW_NUMBER() OVER (PARTITION BY i.AccountID ORDER BY i.OrderID))
    FROM
        inserted AS i
) AS o
WHERE [Order].OrderID = o.OrderID

注意，我们有 READ_COMMITTED_SNAPSHOT ON。它似乎工作了一段时间，但是最近我们注意到 AccountOrderNumber列中有一些重复的值。在分析代码之后，似乎逻辑上可能会出现重复，因为该操作不是原子操作，因此，如果在同一时间添加2个订单，它们将从 TOP 1表中读取相同的 Order值。

注意到重复项之后，我们提出了解决方案2，在解决方案2中，我们有一个单独的表来跟踪每个 AccountOrderNumber的下一个 Account:

DECLARE @NewOrderNumbers TABLE
    (
        AccountID int, 
        OrderID int,
        AccountOrderNumber int
    }

在这种情况下，触发器主体如下:

INSERT INTO @NewOrderNumbers (AccountID, OrderID, AccountOrderNumber)
    SELECT
        I.AccountID,
        I.OrderID,
        ASN.Number + (ROW_NUMBER() OVER (PARTITION BY I.AccountID ORDER BY I.OrderID))
    FROM
        inserted AS I
        INNER JOIN AccountSequenceNumber ASN WITH (UPDLOCK) ON I.AccountID = ASN.AccountID AND ASN.AccountSequenceNumberTypeID = @AccountOrderNumberTypeID


    UPDATE [Order] ...

尽管此解决方案没有创建任何重复项，但由于 @NewOrderNumbers，确实在新创建的 WITH (UPDLOCK)表上造成了死锁。不幸的是，锁定是必要的，以避免重复。

我们的最新尝试(解决方案3)是使用序列。为此，我们需要为系统中的每个 Account创建一个序列，然后在插入新订单时使用它。这是为 AccountID = 1创建序列的代码:

CREATE SEQUENCE Seq_Order_AccountOrderNumber_1 AS INT START WITH 1 INCREMENT BY 1 CACHE 100

以及 AccountID = 1的触发器主体:

    DECLARE @NumbersRangeToAllocate INT = (SELECT COUNT(1) FROM inserted);

    DECLARE @range_first_value_output SQL_VARIANT; 
    EXEC sp_sequence_get_range N'Seq_Order_AccountOrderNumber_1', @range_size = @NumbersRangeToAllocate, @range_first_value = @range_first_value_output OUTPUT; 

    DECLARE @Number INT = CAST(@range_first_value_output AS INT) - 1;
    UPDATE 
        o
    SET 
        @Number = o.AccountOrderNumber = @Number + 1
    FROM 
        dbo.[Order] AS b JOIN inserted AS i on o.OrderID = i.OrderID

使用序列的方法让我们感到担忧，因为我们预计很快就会在系统中拥有100K +个帐户，对于这些帐户中的每个帐户，我们目前需要在6个不同的表中使用这种递增的ID。这意味着我们将获得成千上万个序列，这可能会对整个数据库的性能产生负面影响。我们不知道它是否会产生影响，但是几乎不可能从使用SQL Server中如此多序列的人那里在网上找到任何证词。

最后，问题是:您能想到一个更好的解决方案吗？似乎这应该是一个非常普遍的用例，其中您需要一个针对外键的每个值分别递增的ID。也许我们在这里缺少明显的东西？

我们也欢迎您对以上3种解决方案发表评论。也许其中之一几乎可以接受，只需要进行一些细微调整即可？

Answer 1

可能有更聪明的方法来实现这一目标，但这是我的快速建议。创建一个 View ，该 View 维护最高的帐户订单号；使用schemabinding来提高速度，并使用 View 插入记录。

查看定义:

create view dbo.vwMaxAccountOrderNumber
with schemabinding as
select o.AccountID,
    max(o.AccountOrderNumber) as Sequence
from Orders as o
group by o.AccountID

然后，如果您是 insert...selecting，则将其用于插入语句:

insert into Orders (
    OrderID,
    AccountID,
    AccountOrderNumber,
    ...
    )
select SomeAutoIncrementValue,
       AccountID,
       vmaon.Sequence + row_number() (partition by a.AccountID order by SomeOtherColumn)
from Accounts as a
inner join dbo.vwMaxAccountOrderNumber as vmaon
on vmaon.AccountID = a.AccountID
inner join ...

或像这样 insert...values:

insert into Orders (
    OrderID,
    AccountID,
    AccountOrderNumber,
    ...
    )
values (
    SomeAutoIncrementValue,
    12,
    (select Sequence + 1 from dbo.vwMaxAccountOrderNumber where AccountID = 12),
    ...
    )