Sql-server – Generate an increment ID that is unique for a given value of a foreign key

Disclaimer: This question was asked on SO initially but did not get much traction there, so I am trying here, hoping it will be more interesting to DBAs…

We are working on an ecommerce system where we aggregate orders coming in from different sellers. As you can easily imagine we have an Order table that holds the data for orders of all sellers. Each seller has a unique AccountID which is a foreign key in the Order table.

We want to generate an order number for each order that is coming into the system so that for a given seller (and given AccountID) those order numbers are creating a sequence (first order gets 1, then 2, then 3 etc).

We have tried a couple of solutions to this but they have drawbacks we would like to avoid. All of them are inside a trigger:

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

Our Solution 1 was:

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

Note that we have we have READ_COMMITTED_SNAPSHOT ON. It seemed to work well for a while but recently we noticed some duplicate values in the AccountOrderNumber column. After analysing the code it seems logical that duplicates might appear as the operation is not atomic, so if 2 orders are added at the exact same time they will read the same TOP 1 value from the Order table.

After noticing the duplicates we came up with Solution 2 where we have a separate table to track the next AccountOrderNumber for each Account:

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

The trigger body is a as follows in that case:

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] ...

While this solution did not create any duplicates it did cause deadlocks on the newly created @NewOrderNumbers table due to WITH (UPDLOCK). Unfortunately, the locking was necessary to avoid duplicates.

Our latest attempt (Solution 3) is to use sequences. For this we need to create a sequence for each Account in our system and then use it when a new orders are inserted. Here is the code that creates a sequence for AccountID = 1:

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

And the trigger body for 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

The approach with sequences worries us because we expect to have 100K+ accounts in the system pretty soon and for each of those accounts we currently need that sort of incremented ID in 6 different tables. This means we will end up with hundreds of thousands of sequences, which might have a negative impact on performance of the whole DB. We don't know if it will have any impact, but it is close to impossible to find any testimony on the web from people who have used that many sequences in SQL Server.

Finally, the question is: Can you think of a better solution to the problem? It seems like this should be a pretty common use case where you need an ID that is incremented separately for every value of a foreign key. Maybe we are missing something obvious here?

We will also welcome your comments on the 3 solutions detailed above. Maybe one of them is close to being acceptable and just needs some minor tweaking?