We all have found a time when an identity field, for whatever reason, is the answer to a problem. I hit the problem where I had to perform an identity insert into a table with an ID column on it, and then find the gaps after the table had been populated. To do this, I will use a table variable and the SQL function Ident_Current.
Create and populate the table first
Create Table dbo.Test ( TestID Int Identity (1,1) ,ProductCode Int )
Set Identity_Insert Test On
Insert Test (TestID, ProductCode) Select 1, 123 Insert Test (TestID, ProductCode) Select 3, 1234 Insert Test (TestID, ProductCode) Select 4, 4567 Insert Test (TestID, ProductCode) Select 6, 789
Set Identity_Insert Test Off
And now to find the missing identity columns.
set nocount on
Declare @rows Int
declare @seq table ( seq int not null primary key )
set @Rows = (select Ident_Current('Test'))
declare @i int set @i = 1 while @i <= @Rows begin insert @seq values( @i ) set @i = @i + 1
Select seq From @seq left outer join Test T on seq = T.TestID Where T.TestID Is Null
I use the Ident_Current function to find the last identity inserted on a table, and set a counter to this. I then create a memory table with a sequential number, from 1 to the ident_current value and join that to the live table.
Obviously, this will not be brilliant if you have millions and millions of rows, but it works well on smaller tables