Tuesday, March 9, 2010

Finding Missing Links in auto-incremented id of table-MSSQL Perspective

Finding Missing Auto-Increment Id from table:
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 group by a.id
having a.id+1 < min(b.id)

The Result:
+ ------------+-------------+
|MissingFrom |MissingTill |
+-------------+-------------+
| 2 | 5 |
+-------------+-------------+

Explination:The Explination Can be found In Next Article

No comments:

Post a Comment