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.