Finding Gaps, For Real..

In a previous post I outlined how to find gaps in number using both a common table expression and a sub query.  This was incomplete and misleading to say the least,  In my business we refer to it as an “AD”, accidental discharge.  To present a functional solution the below code will create a temporary table with a single numeric column that is populated with even numbers 1 through 1,000 and finally with the value 9,999 to provide a wide gap in the values.
USE tempdb; GO CREATE TABLE #temp( ID     INT PRIMARY KEY ); GO DECLARE @id INT
SET @id = 1000 WHILE @id > 0 BEGIN 
  IF @id%2 <> 0
   INSERT #temp
   VALUES(@id)
   SET @id = @id – 1
END; GO INSERT #temp(id) VALUES(9999); GO
From this temporary table the goal is to find the numbers missing in the range provided.  In the above temp table there are 499 numbers missing from 1 to 999, all the even numbers, and there are 8,999 missing numbers from 1,000 to 9,998.
This problem with using the ROW_NUMBER() function ordering by the ID column in the temporary table to to provide a contiguous range is that there are only 500 rows in the table and will there by only rank those 500, not the 999 needed.  A numbers table would be ideal for this, but to remain on topic a nested common table expression using CROSS JOINs can be used to provide the contiguous ALL INCLUSIVE range. 
***The nested common table expression is taken from Itzik Ben-Gan and not of my own creation.
The common table expression looks like this:
WITH Nbrs_3( n ) AS (
   SELECT 1 UNION
   SELECT 0
   ),
Nbrs_2( n ) AS (
   SELECT 1
   FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS (
   SELECT 1
   FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS (
   SELECT 1
   FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs  ( n ) AS (
   SELECT 1
   FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) SELECT n FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
      FROM Nbrs ) D ( n ) WHERE n <= 9999
The result of this query is 9,999 rows from 1 to 9,999.  Now we just need to incorporate the ability to drop numbers that exist in the target table, in this case the #temp table, and assure that the full range is accounted from, from 1 to the maximum value in the table 9,999. 
In order to capture the maximum occurring value a variable is declared and value assigned using the MAX aggregate function and immediately followed by the nested common table expression.  The SELECT query calling the common table expression has to be modified to utilize an outer join on the temporary table to the CTE. The sub-query utilizing the ROW_NUMBER() function will assign the upper limit of the range in the WHERE clause using the variable assigned before the CTE. Since the results should only include values not occurring in the temporary table the WHERE clause of the calling query will return only rows where the temporary tables ID column IS NULL.  The final solution looks like this:
DECLARE @max INT
SET @max = (SELECT MAX(ID) FROM #temp); WITH Nbrs_3( n ) AS (
   SELECT 1 UNION
   SELECT 0
   ), Nbrs_2( n ) AS (
   SELECT 1
   FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS (
   SELECT 1
   FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS (
   SELECT 1
   FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs  ( n ) AS (
   SELECT 1
   FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) SELECT CASE
      WHEN r.ID < 2 AND r.ID IS NULL THEN
       1
      WHEN r.ID IS NULL THEN
       rr.n
      ELSE r.ID
      END AS 'MissingID' FROM #temp AS r RIGHT OUTER JOIN
       (SELECT n
          FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) AS n
                FROM Nbrs ) AS D
         WHERE n <= @max) rr ON r.id = rr.n WHERE r.ID IS NULL
The result is 9,498 rows, the even numbers from 2 to 998, and the range from 1,000 to 9,998.