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.

Answers


try this:

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


Need Your Help

Display images in tableViewCell from an array of images

ios uitableview

I have an error message every time when a run this code below to display my image from an array on my cell table view.

Get thumbnail from page? (Concrete 5.7.4.2)

concrete5 concrete5-5.7

As posted here https://www.concrete5.org/index.php?cID=751287 I want to get a thumbnail from a page using the 'old' way.