How To Find Missing Sequence In Identity Column Values In SQL Server?

Introduction :

In this article I will explain how to find missing sequence in identity column values in SQL Server.

Now, We have Create a table with an identity column and some of the identity columns are missing. Here is an example of the table. Due to some reason, we want to find the missing identity columns that have been deleted from it. For example here is the expected output.

Consider the following Students table has StdID as identity column with seed value 1 and increment by 1.

The above identity column StdID has a missing value of 3. Let’s see how to find it.

The following finds the gap in the identity column values:

declare @id int
declare @maxid int
set @id = 1
select @maxid = max(StdID) from Students

create table #StuTemp
(
    id int
)

while @id < @maxid --whatever you max is
begin
    insert into #StuTemp values(@id)

    set @id = @id + 1
end
select 
    s.id as MissingIdentity
from 
    #StuTemp s 
    left join Students t on 
        s.id = t.StdID 
 where t.StdID is null

 drop table #StuTemp

This Statement return 3 as a missing value.

Submit a Comment

Your email address will not be published.

Subscribe

Select Categories