Here you will learn how to find gaps in the identity column values in a table in SQL Server.
Consider the following
Employee table has
EmpID as identity column with seed value 1 and increment by 1.
The above identity column
EmpID has a missing value of 3. Let's see how to find it.
Find Gaps in IDENTITY Column Values
The following finds the gap in the identity column values:
DECLARE @minidentval int; DECLARE @maxidentval int; DECLARE @nextidentval int; SELECT @minidentval = MIN(EmpId), @maxidentval = MAX(EmpID) FROM Employee; SELECT @nextidentval = MIN(EmpID) + IDENT_INCR('Employee') FROM Employee t1 WHERE EmpID BETWEEN IDENT_SEED('Employee') AND @maxidentval AND NOT EXISTS (SELECT * FROM employee t2 WHERE t2.EmpID = t1.EmpID + IDENT_INCR('Employee')) Select @nextidentval as 'Missing Value'
The above SQL will return 3 as a missing value.