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