There are some cases when you don’t want to create physical identity column in the table and want to generate the similar value manually. There are a few methods how to do that. Let’s look at 2 of them.
The first method is the Counters table. Basically you’re creating the table like that:
When you need the new value, you simply get the current one and update it with the new one. It could be wrapped up to the stored procedure like that:
The beauty of this method is the locking – update statement places the exclusive lock on the row so until transaction is active, no other sessions would be able to update the same row. Be careful though with SNAPSHOT isolation level – it would produce an exception during simultaneous access rather than serialize the access.
Let’s test that:
Second method is using identity but from another dummy table:
Let’s see how we can get the single value:
And next – the group of values:
Obviously this method would not protect from the gaps in the values.
You can download the source code from here