This is most common when the identity is not the primary key, and is tracked independently for each Foreign Key Ref.
Field Type OrderID Uniqueidentifier FK to the the order table. LineNumber Int The column that we need each entry to unique per OrderId, but always start from 0. Item Varchar(MAX)
A simple solution, (simplifying the table above to single column) would be:
Lets set up our test environment.DECLARE @X int = null DECLARE @T Table(row INT)
Run this once as is, then run this once with the next two lines commented out.INSERT INTO @T (row) VALUES (0),(1),(2),(3),(4),(5),(6)
Here is the bulk of the actual work that you should learn from.SET @X = 0 IF ( (SELECT COUNT(1) FROM @T) > 0 ) BEGIN SELECT @X = MAX(row) + 1 FROM @T END
And your proofSELECT @X