Compound IDENTITY column in SQL SERVER 2008
- by Asaf R
An Orders table has a CustomerId column and an OrderId column.
For certain reasons it's important that OrderId is no longer than 2-bytes.
There will be several million orders in total, which makes 2-bytes not enough.
A customer will have no more than several thousand orders making 2-bytes enough.
The obvious solution is to have the (CustomerId, OrderId) be unique rather than (OrderId) itself.
The problem is generating the next Customer's OrderId. 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, OrderId) is unique but OrderId 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.
