IDENTITY Summary Column in SQL SERVER 2008

Question

• The Orders table has a CustomerId column and an OrderId column.
• For some reason, it is important that the order ID does not exceed 2 bytes.
• In total there will be only a few million orders, which makes 2 bytes insufficient for the global order identifier.
• The client will have no more than several thousand orders, making 2 bytes enough.
• The obvious solution is to have (CustomerId, CustomerOrderNumber) unique, not OrderId itself.

The problem is created by the following CustomerOrderId. Preferably, without creating a separate table for each client (even if it contains only an IDENTITY column) to keep the top layers of the solution simple.

Q: how do you create the following OrderId so that (CustomerId, CustomerOrderId) is unique, but CustomerOrderNumber itself is allowed to repeat? Does Sql Server 2008 have built-in features for this?

Due to the lack of a better term, I call it the Compound IDENTITY column.

Answer 1

try the following:

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 an unqiue index / constraint for CustomerId, OrderId

Answer 2

I would set a unique restriction on the combination of CustomerId and OrderId.

I think something in this direction should do for you:

    ADD  CONSTRAINT [UQ_CustomerOrders] UNIQUE NONCLUSTERED 
(
    [CustomerId] ASC,
    [OrderId] ASC,
)

Answer 3

, , : SQL Server