SQL Server unique auto-increment column in the context of another column

Question

Suppose the table with two columns:

ParentEntityId int foreign key
Number int

ParentEntityId is a foreign key to another table.

Number is a local identity, i.e. it is unique within single ParentEntityId.

Uniqueness is easily achieved via unique key over these two columns.

How to make Number be automatically incremented in the context of the ParentEntityId on insert?

---

Addendum 1

To clarify the problem, here is an abstract.

ParentEntity has multiple ChildEntity, and each ChiildEntity should have an unique incremental Number in the context of its ParentEntity.

---

Addendum 2

Treat ParentEntity as a Customer.

Treat ChildEntity as an Order.

So, orders for every customer should be numbered 1, 2, 3 and so on.

Answer 1

Well, there's no native support for this type of column, but you could implement it using a trigger:

CREATE TRIGGER tr_MyTable_Number
ON MyTable
INSTEAD OF INSERT
AS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN;

WITH MaxNumbers_CTE AS
(
    SELECT ParentEntityID, MAX(Number) AS Number
    FROM MyTable
    WHERE ParentEntityID IN (SELECT ParentEntityID FROM inserted)
)
INSERT MyTable (ParentEntityID, Number)
    SELECT
        i.ParentEntityID,
        ROW_NUMBER() OVER
        (
            PARTITION BY i.ParentEntityID
            ORDER BY (SELECT 1)
        ) + ISNULL(m.Number, 0) AS Number
    FROM inserted i
    LEFT JOIN MaxNumbers_CTE m
        ON m.ParentEntityID = i.ParentEntityID

COMMIT

Not tested but I'm pretty sure it'll work. If you have a primary key, you could also implement this as an AFTER trigger (I dislike using INSTEAD OF triggers, they're harder to understand when you need to modify them 6 months later).

Just to explain what's going on here:

• SERIALIZABLE is the strictest isolation mode; it guarantees that only one database transaction at a time can execute these statements, which we need in order to guarantee the integrity of this "sequence." Note that this irreversibly promotes the entire transaction, so you won't want to use this inside of a long-running transaction.

• The CTE picks up the highest number already used for each parent ID;

• ROW_NUMBER generates a unique sequence for each parent ID (PARTITION BY) starting from the number 1; we add this to the previous maximum if there is one to get the new sequence.

I probably should also mention that if you only ever need to insert one new child entity at a time, you're better off just funneling those operations through a stored procedure instead of using a trigger - you'll definitely get better performance out of it. This is how it's currently done with hierarchyid columns in SQL '08.