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

SQL Server

By TutorialsTeacher

30 Mar 2022

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.

TutorialsTeacher

Author

We are a team of passionate developers, educators, and technology enthusiasts who, with their combined expertise and experience, create in-depth, comprehensive, and easy to understand tutorials. We focus on a blend of theoretical explanations and practical examples to encourages hands-on learning. Learn more about us