duminică, 16 ianuarie 2011

Finding the first non consecutive value in an identity column

This is an interesting SQL problem that presented itself to me once.
You have this table. It has of course a primary key which is an identity column which has a seed of 1.
Then you insert a some data and then delete some data.
The primary key column(let's call it id from now on) will no longer contain only consecutive numbers.
So the question is: "How do you find the first non consecutive value in this column?"
Well, the first thing that came to my mind at the time was making a cursor. That works too but it's not very efficient.
Here is one implementation using a self join which worked for me:

select top 1 t1.id from [table] t1 left join [table] t2 on t1.id+1 = t2.id where t2.id is NULL

Niciun comentariu:

Trimiteți un comentariu