SQL - Identify missing sequences or Identities of a table

  • Article

There are many ways to identify the missing sequence in a table.  

DEMO TABLE

Lets create a demo table and insert few values inserted into it.

create table  MissingRowDemo (a int);
 
insert into  MissingRowDemo values (7001);
insert into  MissingRowDemo values (7002);
insert into  MissingRowDemo values (7004);
insert into  MissingRowDemo values (7005);
insert into  MissingRowDemo values (7006);
insert into  MissingRowDemo values (7010);
insert into  MissingRowDemo values (7040);
insert into  MissingRowDemo values (7050);

With Recursion

 The below SQL is used with recursion option

/* Using Recursion*/
 
;WITH Missing (missnum, maxid)
AS
(
 SELECT  (select min(a) missnum from  MissingRowDemo) , (select max(a) from  MissingRowDemo)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN  MissingRowDemo tt on  tt.a = Missing.missnum
WHERE tt.a is NULL
OPTION (MAXRECURSION 0);

Without using Recursion option

 The below SQL is used with recursion option

/* No  Recursion*/
 ;with
cte
as (
select a, a - ROW_NUMBER () OVER (ORDER BY  a) as  [diff]
from MissingRowDemo
)
,cte2
as (
select ROW_NUMBER () OVER (ORDER BY  diff) as  [rn]
, MIN  (a) as  [min_a]
, MAX  (a) as  [max_a]
from cte
group by  diff
)
,cteNums --- it may be some permanent auxiliary table
as (
select t1.n * 10 + t2.n as [num]
from
(values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0)) t1 (n),
(values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2 (n)
)
select c1.max_a + n.num
from cte2 c1
join cte2 c2 on c2.rn = c1.rn + 1
join cteNums n on n.num <= c2.min_a - c1.max_a - 1;

Using Lead function

The below SQL 

;with
cte
as (
select a, a - ROW_NUMBER () OVER (ORDER BY  a) as  [diff]
from MissingRowDemo
)
,cte2
as (
select diff
, MIN  (a) as  [min_a]
, MAX  (a) as  [max_a]
from cte
group by  diff
)
,cte3
as (
select max_a, LEAD (min_a) OVER (ORDER BY  diff) as  [next_min_a]
from cte2
)
,cteNums
as (
select t1.n * 10 + t2.n as [num]
from
(values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0)) t1 (n),
(values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2 (n)
)
select max_a + n.num
from cte3
join cteNums n on n.num <= next_min_a - max_a - 1;

Using UDF Function

​

CREATE FUNCTION  dbo.Fn_GenerateNumbers
(@min int,@Max INT)
RETURNS @returnTable TABLE (RowNum INT PRIMARY  KEY)
AS
BEGIN
DECLARE @idt INT
SET @idt = @min
WHILE (@idt < @Max)
BEGIN
    SELECT @idt = @idt + 1
    INSERT INTO  @returnTable
        SELECT @idt
END
RETURN
END
 
 
declare @max int,@min int
select @min=min(id),@max = max(id) from  MissingRowDemo
select T1.RowNum from dbo.Fn_GenerateNumbers (@min,@max) T1
    left outer join  MissingRowDemo T2 on  T1.RowNum = T2.id 
where T2.id  is null