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
