How to find gaps in identity column values in SQL Server?


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:

Example: Find Gaps
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.