Identity-like auto incrementing ID for groups of records

I have a child table associated to the parent by the parent's primary key. Currently, the child table has a standard ID of int IDENTITY(1,1). This is fine for my purposes and that will not change but to the user, they'll be seeing lists of disjointed IDs when they view开发者_高级运维 the child rows associated to the parent.

I would like to have a display ID that starts at 1 and increments (like an identity column) for each group of records.

My child table would look something like this;

ChildID-PK ParentID-FK DisplayID
 1          1           1
 2          1           2
 3          2           1
 4          3           1
 5          1           3

Possible solutions;

• When inserting a record, SELECT MAX(DisplayID) + 1 FROM tbl WHERE ParentID = @ParentID to use as the new records DisplayID. I could see this being problematic if many people are editing a group of records at once.
• Use ROW_NUMBER() to get the DisplayID but, in this case, if a record were to be deleted the DisplayID for existing records could change. This can't happen because users may be referring to specific DisplayIDs when entering data.

---

The safest way I can think of, is creating something similar to Oracle's sequence object. There is a good sample of doing so in this Microsoft SQL Server Development Customer Advisory Team blog post (I prefer option 2).

You should create a sequence for every parent row inserted:

EXEC usp_CreateNewSeq N'ParentSeq' + @parentId

and get the next sequence for each row in your association table:

INSERT INTO [table] VALUES (@childId, @parentId, EXEC 'GetNewSeqVal_'+ @parentId)

I'm not sure about my syntax and I'm not able to check it know, so feel free to correct me.

---

Simpler method: add a column to parent table called MaxChildId with default value = 0. Each time you add a child row you should update this column and use its new value as the DisplayID.

declare @vid int

UPDATE [ParentTable]
SET @vid = MaxChildId+1, MaxChildId = MaxChildId+1
WHERE Id = ParentID
select @vid

This method could cause concurrences when updating parent table.

---

After all being said, I think you better consider a redesign for solving this problem.