Find gaps in identity column values

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.