sql-servert-sqlidentity-column

Table with identity field: Best SQL query to get Ids of deleted records?

I have a table with an identity field. What the best SQL query to get the Ids of all the deleted records from this table?

Solution

  • Left join with a numbers table and grab all the ones where it is null, this uses the built in numbers table but it is better to have your own

    example of what the code would look like

    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)



select number from master..spt_values s
left join #bla b on s.number = b.id
where s.type='P'
and s.number < (select MAX(id) from #bla)
and  b.id is null

    output

    0 3 6 7 8 10 11

    See here: How to return all the skipped identity values from a table in SQL Server for some more detail