How to find Missing Identity Values?

How to find Missing Identity Values?

One of my friends asked me long time ago to get missing identity values. Through power of Row_Number() Window function we can achieve it as below.

--Logic To Identify Missing Values.

Declare @MissingIdentityValues Table

(

      IDCol Int

)

Insert @MissingIdentityValues(IDCol) Values (1) , (2) , (5) , (7) , (8) , (10) , (11) , (15) , (16)

Select MIN(IDCol) StartRange, MAX(IDCol)  EndRange

From

      (

            select IDCol , IDCol -  ROW_NUMBER() OVER(Order By IDCol) as GroupDiff

            from @MissingIdentityValues

      ) AS GroupDiff

Group By GroupDiff