Find missing identity value in a table

Follow below procedure to find the missing identity values in a table:


Example



Declare @intMaxNum int
Select @intMaxNum = max(AccountID) from AccountMaster;


With tempData (result) as
(
Select distinct FG.AccountID + 1 from AccountMaster FG where not exists
(
Select 1 from AccountMaster FGP where FGP.AccountID = FG.AccountID + 1
) and FG.AccountID < @intMaxNum


Union All


Select TD.result + 1 from tempData TD where not exists
(
Select 1 from AccountMaster FGP where FGP.AccountID = TD.result + 1
) and TD.result < @intMaxNum
)
Select result as 'Missing Numbers' from tempData order by result;