Get all the skipped identity values from a table

Today, someone asked me to give a list of all the identity values in a table that have been skipped. The Database have a table with an identity column, the problem with identity columns is that if an insert is rolled back or fails in any way then the identity value could not be reused. Identifying gaps is pretty easy to do if you have a table of numbers in your database.

To get all the skipped identity values I perform certain action.

First I create a table with numbers between 1 and 2048

CREATE TABLE TempNumber(Number INT NOT NULL PRIMARY KEY)

GO

INSERT INTO TempNumber

SELECT Number + 1

FROM Master..spt_values s

WHERE s.Type='P'

Now, I create another table with some numbers WITH Gap

CREATE TABLE #NumberGap(ID INT)

INSERT INTO #NumberGap VALUES(1)

INSERT INTO #NumberGap VALUES(2)

INSERT INTO #NumberGap VALUES(4)

INSERT INTO #NumberGap VALUES(5)

INSERT INTO #NumberGap VALUES(9)

INSERT INTO #NumberGap VALUES(12)

Here is the code that will return the gaps (the values 3,6,7,8,10,11) from the #NumberGap

SELECT Mumber

FROM TempNumber AS T

LEFT JOIN #NumberGap AS N  ON T.Number = N.ID

WHERE T.Number < (SELECT MAX(ID) FROM #NumberGap) 

AND  N.ID IS NULL

Here I had used a simple left join, and also check for the max value otherwise we will get everything back that is greater than the max value in the #NumberGap table.