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

Question

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?

Answer 1

You could use a recursive query:

DECLARE @MaxId int
SELECT @MaxId = SELECT IDENT_CURRENT('MyTable');

WITH Ids AS (
    SELECT 1 AS intId
    UNION ALL
    SELECT intId + 1
    FROM Ids
    WHERE intId < @MaxId
)
SELECT intId
FROM Ids AS i
WHERE NOT EXISTS (
    SELECT NULL FROM MyTable AS m
    WHERE m.intId = i.intId
)
OPTION (MAXRECURSION 0)

Though this won't be very efficient if the table is very large.

Answer 2

A completely different way to do it is this:

SELECT a.intId, b.intId
FROM MyTable a
    CROSS JOIN MyTable b
WHERE a.intId + 1 < b.intId
    AND NOT EXISTS (
        SELECT NULL FROM MyTable c
        WHERE c.intId > a.intId
            AND c.intId < b.intId
    )

Which will give pairs of IDs between which all the records have been removed.

So if the IDs were (1, 2, 3, 6, 7, 12), it would return (3, 6) and (7, 12).

EDIT:

This is very inefficient if the table is large. The following method is much better:

SELECT g.intStartId, MIN(t.intId) AS intEndId
FROM (
    SELECT intId AS intStartId
    FROM MyTable AS a
    WHERE NOT EXISTS (
        SELECT NULL FROM MyTable AS b
        WHERE b.intId = a.intId + 1
    )
) AS g
    CROSS JOIN MyTable AS t
WHERE t.intId > g.intStartId
GROUP BY g.intStartId

So we first find IDs that mark the start of a gap, and then we find the lowest ID we have greater than each to mark the end of the gap.

Answer 3

One option is to create a temporary table/embeddable SQL statement containing all possible IDs (some options are outlined in this article) up to an including the max(identity) of your table.

Then you can left join from this canonical list of values with your table and filter on a null values on the right side.

Answer 4

A query focusing on sequential ID column is not enough. The ID sequence may skip numbers during insert if transaction fails, so if you have ID = (1,3) it does not mean that ID=2 was deleted, it may have been skipped. You have to use something to catch deleted records, like trigger, or OUTPUT DELETED.* -- or use something to compare against, like a snapshot, a backup, a history table.

Answer 5

A completely different approach, which does require a refactoring of your example, would be to not delete from the table, but to have a separate table of deleted item ids (or to have a field in the table showing the row's status). So you could then just select on those data instead. (This would wrap around @Damir's observation that you cannot differentiate between deletions and insert errors.)

Answer 6

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