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.
