Incrementing ID on a SQL row that you cannot use the Identity property with.

This is most common when the identity is not the primary key, and is tracked independently for each Foreign Key Ref.

Example:

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 proof

SELECT @X