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.