How to get missing values from an Identity column?

  • Artigo

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.

Comments