This is most common when the identity is not the primary key, and is tracked independently for each Foreign Key Ref.
|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.|
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)
Here is the bulk of the actual work that you should learn from.
SET @X = 0
(SELECT COUNT(1) FROM @T) > 0 )
SELECT @X = MAX(row) + 1 FROM @T
And your proof