SQL – How to Find Missing Sequence in a table

Posted on December 23, 2014 by Prashanth Jayaram

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:-

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/

View all posts by Prashanth Jayaram →

This entry was posted in Uncategorized. Bookmark the permalink.

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

Yuri Petrov says:

December 28, 2014 at 6:30 pm

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;

Reply
- Yuri Petrov says:
  
  December 29, 2014 at 11:18 am
  
  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;
  
  Reply
- Prashanth Jayaram says:
  
  January 6, 2015 at 8:25 pm
  
  Thanks. Nice Trick.
  
  Reply
Don Simpson says:

December 29, 2014 at 5:04 pm

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

Reply
Edward Jacob Sandet says:

July 20, 2017 at 10:27 am

Thanks for this

Reply