We are working on an e-commerce system in which we combine orders from different sellers. As you can easily imagine, we have an Order table that stores data for orders from 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 included in the system, so for a given seller (and a given AccountID ) these sequence numbers create a sequence (the first order gets 1, then 2, then 3, etc.).
We tried a couple of solutions, but they have flaws that we would like to avoid. All of them are inside the 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 READ_COMMITTED_SNAPSHOT ON . This seemed to work well for a while, but recently we have noticed some duplicate values in the AccountOrderNumber column. After analyzing the code, it seems logical that duplicates can appear, since the operation is not atomic, therefore, if 2 orders are added at the same time, they will read the same TOP 1 value from the Order table.
After we noticed duplicates, we came to solution 2, where we have a separate table for tracking the following AccountOrderNumber for each Account :
DECLARE @NewOrderNumbers TABLE ( AccountID int, OrderID int, AccountOrderNumber int }
In this case, the trigger body is as follows:
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 duplicates, it caused locks in the newly created @NewOrderNumbers table due to WITH (UPDLOCK) . Unfortunately, locking was necessary to avoid duplication.
Our last attempt (solution 3) is to use sequences. To do this, we need to create a sequence for each Account in our system, and then use it when inserting new orders. Here is the code that creates the 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 sequence approach bothers us because we expect to have 100K + accounts on the system pretty soon, and for each of these accounts we currently need this type of increased identifier in 6 different tables. This means that we get hundreds of thousands of sequences that can adversely affect the performance of the entire database. We don’t know if this will have any effect, but it’s almost impossible to find any evidence on the Internet from people who used this many sequences in SQL Server.
Finally, the question arises: can you think of a better solution to the problem? It seems like this should be a fairly common use case when you need an identifier that is incremented separately for each foreign key value. Maybe we are missing something here?
We also welcome your comments on the 3 solutions described above. Maybe one of them is close to being acceptable and just needs some minor tweaking?