Skip to main content

Find missing number from identity column

DECLARE @start DATETIME, @end DATETIME, @nextstart DATETIMESET @start = getdate()-- I created a database MyTestDB for the purpose of testing.USE MyTestDB-- Create a table MyRowNumbers and populate itCREATE TABLE MyRowNumbers(RowNumber int primary key)DECLARE @counter intset @counter = 1WHILE @counter <= 10000 BEGIN INSERT MyRowNumbers(RowNumber) VALUES(@counter) SET @counter = @counter+1 END-- Now I will delete some rows in between in MyRowNumbers to do my missing numbers queryDELETE MyRowNumbersWHERE (RowNumber = 5 or RowNumber = 11 or RowNumber = 34)SET @nextstart = getdate()-- Now I want to extract a list of the missing numbers in MyRowNumbersSELECT a.RowNumber+1 MissingNumbers from MyRowNumbers a-- doing a left join to the same tableleft join MyRowNumbers b on b.RowNumber = a.RowNumber+1WHERE b.RowNumber IS NULL AND a.RowNumber+1 < 101SET @end = getdate()-- The duration of this query in milliseconds. Compliments Jeff Moden.
SELECT DATEDIFF(ms,@start,@end) fullduration, DATEDIFF(ms,@nextstart,@end) listduration