The CSS SQL Server Engineers have posted an interesting post, I myself don’t have any of the fake identity columns but I did many times suggest in newsgroups to use UPDLOCK and HOLDLOCK in a transaction to guarantee that 2 inserts would not generate the same key value. This stuff below is from the CSS SQL Server Engineers post here: http://blogs.msdn.com/psssql/archive/2009/10/26/reduce-locking-and-other-needs-when-updating-data-better-performance.aspx
The following pattern typically stems from an old practice used in SQL 4.x and 6.x days, before IDENTITY was introduced.
begin tran declare @iVal int select @iVal = iVal from CounterTable (HOLDLOCK) where CounterName = 'CaseNumber' update CounterTable set iVal = @iVal + 1 where CounterName = 'CaseNumber' commit tran return @iVal
This can be a dangerous construct. Assume that the query is cancelled (attention) right after the select. SQL Server treats this as a batch termination and does not execute other statements. The application now holds a lock under the open transaction and without proper handling it leads to blocking.
One Statement Fix
declare @iVal int update CounterTable set @iVal = iVal = iVal + 1 where CounterName = 'CaseNumber' return @iVal
So what do you think? Will you use that in the future? Is this too dangerous/unfamiliar?
Take a look at this piece of code that was shown to me by Emtucifor in this forum post: http://forum.lessthandot.com/viewtopic.php?f=17&t=7601&start=50#p41079
So what do you think? Good or might be prone to some kind of bug in the future and then what?