How to find missing (GAPS) sequence numbers

Few years back I wrote a solution for the same problem here. Recently I read an article by Henning on the similar topic which was interesting and informative.

After that, I was wondering why not just make use of a NUMBER table and a NOT IN to do this. I am providing the test script as-is and I haven't compared the performance yet between any other solutions out there :)

--Lets set up a sample table and pump in few thousand records with gaps in it
SET NOCOUNT ON;
GO

CREATE TABLE tblFindGaps 
(
    ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    [Name] VARCHAR(50) NULL
)
GO

INSERT INTO tblFindGaps (Name) VALUES ('a')
GO 10000

-- Delete some rows to create gaps in the identity column
DELETE tblFindGaps WHERE ID IN (23, 64, 3392, 44)
DELETE tblFindGaps WHERE ID BETWEEN 5000 AND 6000
GO

Solution:

Lets create a table to hold numbers from 1 till the MAX available number already present in tblFindGaps table.

-- Number table 

Create table tblNumbers

(

iValue INT iDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

[dummy] char(1)

)

--Find the maximum value of the sequence column of your table

SELECT MAX(ID) from tblFindGaps

GO

INSERT INTO tblNumbers ([dummy]) VALUES ('a')

GO 10000 -- Assign the MAX value here

--Query to list the missing sequence numbers
SELECT  *
FROM 
(
SELECT ROW_NUMBER() OVER(ORDER BY iValue) AS Number FROM tblNumbers
) temp
where Number not in 
(
SELECT [ID] FROM tblFindGaps
)
GO