Monthly Archives: February 2020

Find missing ID’s in table

This script search in a TABLE for missing ID's "auto-incremental primary key" or IDENTITY gaps as some people call them.

-- SETUP DEMO
DROP TABLE IF EXISTS #TEST;
CREATE TABLE #TEST 
(
	ID INT
)
INSERT INTO #TEST (ID)
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)

DELETE FROM #TEST WHERE ID = 1
DELETE FROM #TEST WHERE ID = 4

-- show demo data
SELECT * FROM #TEST

-- Find missing ID
;WITH CteID AS
(
	SELECT ID
	FROM #TEST AS A
)
SELECT i.ID-1 AS MISSING_ID
FROM CteID as i
LEFT JOIN CteID as ii ON i.ID = ii.ID+1
WHERE i.ID - ISNULL(ii.ID,0) <> 1
GO