How to get missing values from an Identity column?



Let us assume that there is an Employee table with EmployeeId as the Identity column in which some values are missing.  The following batch will show all the missing values in that Identity column. The logic I preferred is to create another table with the same increment as that of the parent table but without any gaps. Finally, subtracting the parent table rows from the newly created table gives us the missed values. Rest of the script is pretty straight forward and self explanatory.


 Declare @MinEmpId int


Declare @MaxEmpId int


Declare @sql nvarchar(250)


select @MinEmpId = Min(EmployeeID),@MaxEmpId = Max(EmployeeID) from Employee


SET @sql = 'Create Table Temp1(Id int IDENTITY(' + cast(@MinEmpId as varchar(25))+ ',1),DummyCol int)'


EXEC sp_executesql @sql


while(@MinEmpId <= @MaxEmpId)


begin


Insert into Temp1(DummyCol) Values(1)


set @MinEmpId = @MinEmpId + 1


end


Select Id from Temp1


Except


Select EmployeeId from Employee


Drop Table Temp1


The above script can be made reusable by making it a stored procedure using Dynamic Sql as shown below. It just accepts table name as a parameter and does the rest of the job.


CREATE PROC GetMissingIds(@TableName Varchar(50))


AS


BEGIN


Declare @MinEmpId int


Declare @MaxEmpId int


Declare @Increment int


Declare @sql nvarchar(250)


SET @sql = 'SELECT @Increment=IDENT_INCR(''' + @TableName + ''')'


EXEC sp_executesql @sql, N'@Increment Int OUTPUT', @Increment OUTPUT


SET @sql = 'select @MinEmpId=Min($Identity),@MaxEmpId=Max($Identity) from ' + @TableName


EXEC sp_executesql @sql, N'@MinEmpId Int OUTPUT,@MaxEmpId Int OUTPUT',@MinEmpId OUTPUT,@MaxEmpId OUTPUT


SET @sql = 'Create Table Temp1(Id int IDENTITY(' + cast(@MinEmpId as varchar(25))+ ',' + cast(@Increment as Varchar(10)) + '),DummyCol int)'


EXEC sp_executesql @sql


 


while(@MinEmpId <= @MaxEmpId)


begin


Insert into Temp1(DummyCol) Values(1)


set @MinEmpId = @MinEmpId + @Increment


end


SET @sql = 'Select Id from Temp1 EXCEPT Select $Identity From ' + @TableName


EXEC sp_executesql @sql


 


Drop Table Temp1


 


END


Hope this helps.


Skip to main content