Wednesday, November 3, 2010

Missing IDs in an integer sequence (the gap problem)

I encountered a missing integer ID problem today and rather than reinventing the wheel found the following script to deal with it.

Finding (all the) gaps in an identity column (or any integer based column for that matter) using Sql 2005

I have added OPTION (MAXRECURSION 0) to the end of the SELECT statement so that the CTE can be called as many times as necessary.

declare @i int;
 SELECT @i = MAX(pkid) FROM t1;
 WITH tmp (gapId) AS (
   SELECT DISTINCT a.pkid + 1
   FROM t1 a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.pkid + 1)
   AND a.pkid < @i
   UNION ALL
   SELECT a.gapId + 1
   FROM tmp a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.gapId + 1)
   AND a.gapId < @i
 )
 SELECT gapId
 FROM tmp
 ORDER BY gapId
OPTION (MAXRECURSION 0) ;
A good article on other sequence generation scripts is Creating a Number (Sequentially incrementing values) table in T-SQL

No comments: