SQL – How to Find Missing Sequence in a table

Problem statement:-

 A table with identity column have ‘n’ rows in that few rows are deleted. How to find which rows are deleted?

Download code :- MissingRows

Solution:-

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);
  

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);

Output:-

MissingRows

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to SQL – How to Find Missing Sequence in a table

  1. Yuri Petrov says:

    Example of solution without 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, you know
    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;

    • Yuri Petrov says:

      Even better with SS2012:
      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;

  2. Don Simpson says:

    Quick and dirty version using tally table:

    select tt.i
    from dbo.TalleyTable((select max(i) from MissingRowDemo)) tt
    left join MissingRowDemo mrd
    on tt.i = mrd.i
    where tt.i between (select min(i) from MissingRowDemo) and (select max(i) from MissingRowDemo)
    and mrd.i is null

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s