"Identify Missing Identity Values"

-- Step #1: Create Table and Populate with ValuesCREATE TABLE #CarType ([ID]
[Name] INT IDENTITY, 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 IDsDELETE 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 ValuesDECLARE
DECLARE
DECLARE @ID INT @MaxID INT @MissingCarTypeIDs TABLE ( [ID] INT )SELECT @MaxID = [ID] FROM #CarTypeSET @ID = 1WHILE @ID <= @MaxIDBEGIN
IF NOT EXISTS (SELECT 'X' FROM #CarTypeWHERE [ID] = @ID)INSERT INTO @MissingCarTypeIDs ( [ID] )VALUES ( @ID )SET @ID = @ID + 1END
SELECT * FROM @MissingCarTypeIDs