Identify Missing Identity Values

There will come a time wherein a table that contains an identity column will have some IDs deleted. These deleted IDs will create gaps between the values of the identity column. To create a sequential identity column with no gaps in between, newly created records of the table can make use of unused or deleted IDs. But before it can make use of these IDs, you first have to identify these missing IDs. This article will discuss 3 ways of identifying the missing identity values in a table containing an identity column.

To simulate the process of determining missing identity values in a table, let's assume we have the following table:

-- Step #1: Create Table and Populate with Values
CREATE TABLE #CarType (
    [ID] INT IDENTITY, 
    [Name] VARCHAR(20) )

INSERT INTO #CarType ( [Name] ) VALUES ( 'Bentley' )
INSERT INTO #CarType ( [Name] ) VALUES ( 'BMW' )
INSERT INTO #CarType ( [Name] ) VALUES ( 'Ferrari' )
INSERT INTO #CarType ( [Name] ) VALUES ( 'Lamborghini' )
INSERT INTO #CarType ( [Name] ) VALUES ( 'Hummer' )
INSERT INTO #CarType ( [Name] ) VALUES ( 'Jaguar' )
INSERT INTO #CarType ( [Name] ) VALUES ( 'Lexus' )
INSERT INTO #CarType ( [Name] ) VALUES ( 'Mercedes Benz' )
INSERT INTO #CarType ( [Name] ) VALUES ( 'Porsche' )
INSERT INTO #CarType ( [Name] ) VALUES ( 'Volvo' )

SELECT * FROM #CarType

The output of the SELECT statement will be as follows:

ID	Name
1	Bentley
2	BMW
3	Ferrari
4	Lamborghini
5	Hummer
6	Jaguar
7	Lexus
8	Mercedes Benz
9	Porsche
10	Volvo

Let's say certain records have been deleted from the table, as shown in the following script:

-- Step #2: Delete IDs
DELETE FROM #CarType WHERE [ID] IN (3, 4, 9)

SELECT * FROM #CarType

The table now has the following records:

ID	Name
1	Bentley
2	BMW
5	Hummer
6	Jaguar
7	Lexus
8	Mercedes Benz
10	Volvo

One way to identify the missing/deleted identity values is to loop through the table starting from 1 up to the maximum identity value as shown in the following script:

-- Step #3 (Option #1): Identify Missing IDENTITY Values
DECLARE @ID INT
DECLARE @MaxID INT
DECLARE @MissingCarTypeIDs TABLE ( [ID] INT )

SELECT @MaxID = [ID] FROM #CarType

SET @ID = 1
WHILE @ID <= @MaxID
BEGIN
    IF NOT EXISTS (SELECT 'X' FROM #CarType
                   WHERE [ID] = @ID)
        INSERT INTO @MissingCarTypeIDs ( [ID] )
        VALUES ( @ID )

    SET @ID = @ID + 1
END

SELECT * FROM @MissingCarTypeIDs

The missing identity values are temporarily stored in a table variable for later processing. The output of the SELECT statement above is as follows, which are the deleted identity values:

ID
3
4
9

Another way of determining the missing identity values is the use of a temporary table that contains just one column which will hold all possible values of an identity column from a value of 1 to the maximum identity value of the table being searched.

-- Step #3 (Option #2): Identify Missing IDENTITY Values
DECLARE @IntegerTable TABLE ( [ID] INT )
DECLARE @ID INT
DECLARE @MaxID INT

SELECT @MaxID = [ID] FROM #CarType

SET @ID = 1
WHILE @ID <= @MaxID
BEGIN
    INSERT INTO @IntegerTable ( [ID] )
    VALUES ( @ID )

    SET @ID = @ID + 1
END

SELECT A.*
FROM @IntegerTable A LEFT OUTER JOIN #CarType B
  ON A.[ID] = B.[ID]
WHERE B.[ID] IS NULL

The first part of the script is the population of a table variable (@IntegerTable) that contains one column (ID), which will hold all possible values from 1 to the maximum identity value of the table (@MaxID).

The second part of the script is the joining of this table variable with the table being determined the deleted identity values. Since the table variable contains all possible identity values, a LEFT OUTER JOIN is used and the deleted identity values are identifed by checking for NULL IDs from the table (WHERE B.[ID] IS NULL).

Instead of a table variable, a user-defined table-valued function can be created that will return a table containing the same data as the table variable used above. A separate article discusses such function, Integer Range Table Function. Using the same function discussed on the mentioned article, [dbo].[ufn_GenerateIntegers], the script can be simplified as follows:

-- Step #3 (Option #3): Identify Missing IDENTITY Values
DECLARE @MaxID INT

SELECT @MaxID = [ID] FROM #CarType

SELECT A.*
FROM [dbo].[ufn_GenerateIntegers] ( @MaxID ) A LEFT OUTER JOIN #CarType B
  ON A.[IntValue] = B.[ID]
WHERE B.[ID] IS NULL