SQL - Multiple Independent Auto-Increments

Say that you need to have a table with independent incrementing values based on a separate in-table key. For example, you may be operating a sales system, and the business wants each store to have it's own set of incrementing order numbers. What is the best way to implement this system on SQL Server?

Sequences

If you are operating SQL Server 2012 or later, you could use Sequences. However, this would require creating a new sequence for each store, which means the database user would need the CREATE SEQUENCE permission. Also, the administrative headache for having more than a few sequences would likely be onerous.

CurrentOrderNumber

If the key value is a reference to another table, e.g. if the key SalesOrder.StoreId references the Store table, then you could store in the referenced table the current increment value, like so:

CREATE TABLE Store  
(
    StoreId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    StoreNumber INT NOT NULL UNIQUE,
    CurrentOrderNumber INT NOT NULL,
);

Getting the current order number is as easy as

UPDATE Store SET CurrentOrderNumber = CurrentOrderNumber + 1 OUTPUT inserted.CurrentOrderNumber WHERE StoreId = @StoreId;  

Doing the update and returning inserted.CurrentOrderNumber prevents race conditions between obtaining an order number and the order number being updated. You can be sure with this that the order number obtained is valid for the order and for no one else. However, this requires going to the server twice: once to get the order number, and again to save the new order. The best way to get around this would be to create a UDF dbo.GetCurrentOrderNumber(@StoreId), which can be called during the INSERT statement of the new order. Then only one round trip is required to save a new sales order to the database. The other downsides to this method are:

• It requires recording the current order number outside of the SalesOrder table which has the potential for data integrity issues. If you can guarantee that all code creating new orders uses the UDF, then this should be a limited risk.
• It creates a bottleneck for obtaining the order number under load for a single store. This is unlikely to be an issue for most implementers; the level of scale required to experience this bottleneck would require multiple users at the same store frequently creating new tickets.

NOLOCK

Suppose SalesOrder is constructed as follows:

CREATE TABLE SalesOrder  
(
    SalesOrderId INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    StoreId INT NOT NULL,
    OrderNumber INT NOT NULL,
    ...
);

CREATE UNIQUE CLUSTERED INDEX SalesOrderUK  
ON SalesOrder(StoreId, OrderNumber);  

Then when you create a new sales order, you can insert as follows (under normal READ COMMITTED locking rules):

INSERT SalesOrder(StoreId, OrderNumber, ...)  
VALUES (  
    @StoreId,
    (SELECT MAX(OrderNumber) + 1 FROM SalesOrder WITH (NOLOCK) WHERE StoreId = @StoreId),
    ...)
OUTPUT inserted.StoreId, inserted.OrderNumber, ...;  

The NOLOCK hint forces the query engine to read past all locks to find all sales orders for the specified store, no matter which transaction the sales order was entered on. Normally, NOLOCK is very dangerous, because we may rely on data that has not been committed, and may not have full integrity. In this case, it allows us to prevent two orders from using the same OrderNumber. Once a sales order has been entered into the table, even if the transaction is still open (so that we can save details to the order, for example), it is visible to NOLOCK. By clustering on StoreId, OrderNumber, the engine can rely on the index to give us a value quickly.

This method does not rely on recording the current order number by store, so the value returned is always accurate. There is no bottleneck for retrieving the number because of the NOLOCK. Performance is quick because of the index, which allows the engine to look at the final record for the store instead of scanning through all records for the store. It does not require going to the server twice, because the order number is provided as part of the INSERT statement.

Conclusion

Preferably, the business will not require this solution. There are few cases where there is an advantage to each store having it's own order numbering. In fact, in most cases, businesses are better served by not using a sequential ordering at all: sequential ordering allows customers and competitors to know approximately how many sales are being completed by the company. However, on the off-chance such a requirement exists, here are a few ways to make it happen.