Compound IDENTITY column in SQL SERVER 2008
- An Orders table has a CustomerId column and an OrderId column.
- For certain reasons it's important that an order's id is no longer than 2-bytes.
- There will be several million orders in total, which makes 2-bytes not enough for a global order id.
- A customer will have no more than several thousand orders making 2-bytes enough.
- The obvious solution is to have the (CustomerId, CustomerOrderNumber) be unique rather than OrderId itself.
The problem is generating the next CustomerOrderId. Preferably, without creating a separate table for each customer (even if it contains only an IDENTITY column), in order to keep the upper layers of the solution simple.
Q: how would you generate the next OrderId so that (CustomerId, CustomerOrderId) is unique but CustomerOrderNumber itself is allowed to have repetitions? Does Sql Server 2008 have a built in functionality for doing this?
For lack of a better term I'm calling it a Compound IDENTITY column.
DECLARE @Output table (orderID smallint) --smallint=2 bytes BEGIN TRANSACTION INSERT INTO ORDERS (CustomerId ,OrderId ,.....) OUTPUT INSERTED.OrderId INTO @Output SELECT @CustomerId ,ISNULL(MAX(OrderId),0)+1, @... FROM ORDERS WITH (UPDLOCK,HOLDLOCK) WHERE CustomerId=@CustomerId --any other processing, can use just generated @Output.OrderId value if necessary COMMIT
make sure you have a unqiue index/constraint on CustomerId ,OrderId
I would set a Unique Constraint on the combination of CustomerId and OrderId.
I thing something along these lines should do for you:
ADD CONSTRAINT [UQ_CustomerOrders] UNIQUE NONCLUSTERED ( [CustomerId] ASC, [OrderId] ASC, )
this will probably help you: Custom Auto-Generated Sequences with SQL Server