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?

CodePudding user response：

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);