Q:

Using Identity in SQL Server without race condition

So let's say you have a table of Patients with an IDENTITY(1,1) for the primary key.  By using @@Identity, how do we avoid a race condition where two people may save a new patient at the same time?  Obviously, duplicate ID's in the Patients table would not be created, but what if the application needed the ID for one of the inserted patients to update a record in another table elsewhere?  How do we know that @@Identity won't get the ID of the other record if both are inserted at the same time?
Or is there a best practice for avoiding this?
JamesNT

A:

Yes, there is a best practice.  Don't use @@Identity.
The safest way to get the identity values assigned in an insert statement is to use the OUTPUT clause.  You should start with the documentation.
This has numerous advantages:

It does not get confused by triggers and nested statements.
It can handle multiple inserts at the same time.
It can return the values of other columns, not just the identity column.
It specifically returns the rows affected by the transaction, so you don't even think about sessions, users, or anything else.

