SQL SERVER – Find Missing Identity Values

To find missing identity values in a table in SQL Server, you can use a query like the following:

WITH cte AS (
   SELECT MIN(id) AS min_id, MAX(id) AS max_id
   FROM your_table
)
SELECT a.id
FROM cte
CROSS JOIN (SELECT a.id + 1 AS id
            FROM (SELECT MIN(id) AS id FROM your_table) a
            UNION ALL
            SELECT b.id - 1
            FROM (SELECT MAX(id) AS id FROM your_table) b
           ) a
LEFT JOIN your_table t ON a.id = t.id
WHERE t.id IS NULL

This query uses a common table expression (CTE) to determine the minimum and maximum identity values in the table, and then generates a list of all the values between those two values using a cross join and a union all. Finally, it uses a left join to find any values that are not present in the table.

Note that this query will only work if the identity column is contiguous, meaning there are no gaps between the values. If there are gaps, this query will not be able to find them.

You can also use this query to find missing identity values in a range of values by modifying the CTE to select the range of values you are interested in, and adjusting the query to use those values as the minimum and maximum values.



Regenerate response