How to Find my Missing Identity

IDENTITY is a property of a numeric data type that tells SQL Server it is an auto-incrementing column. It increments every time a new record is inserted to the table.

An identity column can not start with value less than the seed and will always increment by the identity increment property. Although an identity column may be unique, it is not always sequential. If a record is deleted from the table, the deleted value will not be reuse, even if it's the last record.

Because it's not always sequential, it's possible that there are missing value on the column that breaks the sequence. Here's a query that could check for the missing sequence in an identity column:

select a.IdentityColumn+1
from TableName A
left join TableName B on A.IdentityColumn+1 = B.IdentityColumn
where B.IdentityColumn is null

~~CK