Every now and then someone will ask how to return a list of all the identity values in a table that have been skipped. You will probably have a table with an identity column, the ‘problem’ with identity columns is that if an insert is rolled back or fails in any way then the identity value is not reused…you end up with gaps. Identifying gaps is pretty easy to do if you have a table of numbers in your database.

If you don’t have a table of numbers, here is some code that will create a table with numbers between 1` and 2048

create table Numbers (number int not null primary key )
go
insert Numbers 
select number + 1 
from master..spt_values s
where s.type='P'

Now that we have our numbers table we can proceed with creating another table which we will populate with some numbers

create table #bla(id int)

insert #bla values(1)
insert #bla values(2)
insert #bla values(4)
insert #bla values(5)
insert #bla values(9)
insert #bla values(12)

Here is the code that will return the gaps (the values 3,6,7,8,10,11) from the temp table

select number 
from Numbers n
left join #bla b on n.number = b.id
where n.number < (select MAX(id) from #bla)
and  b.id is null

As you can see it is a simple left join, we also check for the max value otherwise you would get everything back that is greater than the max value in the #bla table.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum