1)Step First:
We first create table as follows:
create table missingGap(id int identity(1,1),val varchar(50))
2)Step Two:
Now we have to insert few records ,it’s quite state forward
insert into missingGap(val)values(1)
insert into missingGap(val)values(2)
insert into missingGap(val)values(3)
insert into missingGap(val)values(4)
insert into missingGap(val)values(5)
insert into missingGap(val)values(6)
insert into missingGap(val)values(7)
3)Step Three :
Lets see what is in data store:
select * from missingGap
id Val
1 1
2 2
3 3
4 4
5 5
6 6
7 7
4) Step Four :
Delibrately Create A gap in auto increment id by deleting few records
delete from missinggap where id in (2,3,4)
id Val
1 1
5 5
6 6
7 7
5) Step Five:
Lets finding the missing Entry:
select a.id+1 MissingFrom,min(b.id)-1 MissingTill from missingGap a,missingGap b
where a.id
having a.id+1 < min(b.id)
The Result:
+ ------------+-------------+
|MissingFrom |MissingTill |
+-------------+-------------+
| 2 | 5 |
+-------------+-------------+
Explination:The Explination Can be found In Next Article
