Hi all,
I have a client that absolutely insists that their database should have no Identity columns, and has manually implemented his own incrementer in a stored procedure that creates table rows. It has been written so that the next ID value is determined and then used in an insert statement. Obviously this is vulnerable to a host of problems.
So here's my question: Assuming that the usual, automatic, method is out of the question, what is the best way to implement an identity column in SQL?
Assuming that the usual, automatic, method is out of the question, what is the best way to implement an identity column in SQL?
By definition, the usual, automatic method is using the identity column. Everything else is a crappy way to do that. Talk to your client about this. Assuming that they have a reason or misconception for doing that, you can help them fix the cause or clarify that identity is not a dangerous monster.
If you have to do this anyway, make sure the task of keeping track of the identity (incrementing it) and inserting new rows to the table is done in a single transaction with appropriate isolation levels. (and start looking for a new job)
I've seen this when customers want numeric id's that are consecutive across tables, so that tableA has rows with ids 1,2,3 and tableB has rows with ids 4,5,6. They achieved this by basically having a NextId table, which as one might conjecture contains the next id to be used.
If you can't slap some saneness into them, this is one possible approach with no real upside except that you pleased your client.