Am I able to use the OUTPUT inserted.identity value obtained after an insert during an SQL transaction (or does that even return a value)?
Here's pseudo-code to explain the situation
Begin SQLTransaction (Using .NET SQLTransaction)
- Insert into table output identity field of inserted
- Insert into another table using the above command's output identity.
End transaction
I'm worried that since this is in a transaction and the first insert query is not actually committed to the database, the outputted identity column of the first command will possibly not be valid.
Is there a guarantee that the output identity of an INSERT during a transaction will be the same as what is committed after the transaction? Or does SQL prohibit OUTPUT INSERTED.x during transactions?
Yes, the OUTPUT clause will behave exactly like SCOPE_IDENTITY().
After the first INSERT statement, the record will have been inserted into the database, just not yet committed. At this point the identity will never change.
If the transaction were to be rolled back however, the identity will be lost forever (leaving a gap)