Insert with id in range

Question

I have a table with unique id column being able to accept only values from 0 to 255. When rows are removed, their ids can be reused. For inserting I know I can just generate numbers in my app in a loop until succeeded but is there a better way to generate such ids at database side? How will it interact with concurrent transactions? Should I use some specific isolation level for this?

Answer 1

To find the lowest Id to insert you can simply find the minimum value for which the next expected value does not exist

select Coalesce(Min(id), 0) + 1
from t
where not exists (select * from t t2 where t2.id = t.id + 1);