2007-04-27

Gaps in Identity Columns.

The recent tip at MSSQLtips.com covers just the topic in the heading.
But the problem solved in the body of the article is not actually the one stated. The resulting output from the script produces the list of all missing IDs. While being a decent academic exercise the result has not practical value not to mention is uses SQL 2005 features, which limits its application. With the same success somebody could use simply

...WHERE some_table.pkid NOT EXISTS IN(SELECT pkid FROM t1)

If we take a slightly different approach with temp tables, we can simplify things a lot and get all the information DBA may need for the analysis.
First, add all the ids to a temp table with IDENTITY column

CREATE TABLE #gaps
SELECT a.pkid, GapSize = pkid - case
FROM t1 a
WHERE NOT EXISTS(
SELECT * FROM t1 b
WHERE b.pkid = a.pkid + 1 )
AND a.pkid < (select max(pkid) from t1);