stephen.lear (2/24/2011)
Does your solution really solve the issue?What if the requirement was for no gaps in the key sequence? Doesn't using identities in this way lead to gaps in the key sequence if a transaction is rolled back.
There is often a requirement to keep a continuous sequence, such as when assigning invoice numbers, in which case the original USP was correct, but your replacement may lead to gaps in sequence.
True, using Identities in transactions means a rollback of the transaction will not rollback the incremented value. For the application I was working with, this was not an issue at all. As long as the value obtained was unique, there was no problem. The goal of this exercise was to improve the concurrency in the database.
Ajit Ananthram
Blog - http://ajitananthram.wordpress.com