Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number?
This is covered in the SQL Server product documentation:
CREATE TABLE (Transact-SQL) IDENTITY (Property)
Identity columns can be used for generating key values. The identity property on a column guarantees the following:
- Each new value is generated based on the current seed & increment.
- Each new value for a particular transaction is different from other concurrent transactions on the table.
The second point is the guarantee you need.
It is valid no matter how many user processes or threads access SQL Server concurrently.
To state it explicitly: yes, identity allocation is thread-safe.
A call to
CMEDSeqGen::GenerateNewValue allocates the next value to a caller, and is protected by a latch (a lightweight synchronization object). It is not affected by user transactions, which means you can end up with gaps in case of rollbacks. Concurrent transactions can generate identity (and sequence) values without having to wait on a lock.
For more details, see my answer to a related question.