Wednesday 25 July 2012

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

No comments: