Identity-like auto incrementing ID for groups of r

问题:

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.

回答1:

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.