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:-
Prashanth Jayaram 
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;
LikeLike
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;
LikeLike
Thanks. Nice Trick.
LikeLike
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
LikeLike
Thanks for this
LikeLike