I have a @Tab table, which has an ID column. In this column, some values are missing between the first and last value of the column.
Now, I will try to find out the all the missing values between 1 and 16.
Query
  1. DECLARE @Max int;
  2. Declare @Min int;
  4. SET @Min=(SELECT MIN(t.Id) FROM @Tab t);
  5. SET @max=(SELECT MAX(t.Id) FROM @Tab t);
  6. WITH CTE AS
  7. (SELECT @Min AS Col
  8. UNION ALL
  9. SELECT COl+1 as t FROM CTE c
  10. WHERE COl<@max)
  11. SELECT * FROM CTE WHERE CTE.Col NOT In(SELECT Id FROM @Tab t);
Output


This query returns 4 values: 3, 6, 9, 14. These four values are missing in ID column of @Tab.