I have tried this scheme and there always seemed to be some level of locking contention that caused an issue. The only truly reliable way to do what you ask is to write the value to, within a stored procedure:

Read the max value of your central key table.

Write the next value,

get the max value again

compare first value +1 to this value

return the current max to the calling application.


You would obviously have to decide what to do if you ran into collision.

These things are tedious to control, and a problem always seems to arise that you did not think of.