SQL SERVER 2008中的复合身份列

Question

• 一个订单表有一个CustomerId列和一个OrderId列。
• 由于某些原因，订单编号不应超过2个字节的长度非常重要。
• 总共将有数百万个订单，这使得2个字节对于全局订单ID来说不够用。
• 一个客户最多只有几千个订单，2个字节足够了。
• 显而易见的解决方案是让（CustomerId，CustomerOrderNumber）成为唯一标识，而不是OrderId本身。

问题在于如何生成下一个CustomerOrderId。最好不要为每个客户创建单独的表（即使它只包含IDENTITY列），以便保持解决方案的上层简单。

问：您将如何生成下一个OrderId，以便(CustomerId，CustomerOrderId)是唯一的，但允许CustomerOrderNumber本身重复？SQL Server 2008是否具有内置功能来执行此操作？

出于缺乏更好的术语，我称之为复合IDENTITY列。

- Asaf R

---

“OrderID” 可能更好地命名为 “CustomerOrderNumber”，因为它本身并不能标识一个独立的订单。 - D'Arcy Rittich

@OrbMan：你说得对。我已经改了。 - Asaf R

Answer 1

试一下这个：

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

请确保在CustomerId和OrderId上拥有唯一的索引/约束条件。

- KM.

Answer 2

我会在 CustomerId 和 OrderId 的组合上设置唯一约束。

我认为以下代码可以帮到你：

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

- AllenG

---

每次插入时，OrderId将如何递增？ - KM.

@AllenG：这是个好的开始，但我该如何确定下一个值？ - Asaf R

要创建新的ID，您可能需要使用KM的解决方案。我理解问题是如何确保SQL2k8在允许列内重复的同时确保组合唯一。 - AllenG

好的。那也很有用。谢谢！ - Asaf R

Answer 3

这可能会对你有所帮助：使用SQL Server自定义自动生成序列

- Mladen Prajdic

---

+1：它为任何选择的解决方案提供了几种好的方法和原则。 - Asaf R