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?
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.
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
SalesOrdertable 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.
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, ...;
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
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.