There are many ways in SQL Server to get Identity value, however OUTPUT clause is a safe way to get the most exact last inserted value into the IDENTITY column of a table
Problem & Recommendation
While adding row into table that has an identity column, Identity value may be interfered with other value in other scope or session.
When inserting a row into a database table that contains an identity column, I need a way to capture the identity value generated by the database engine after it inserts the row into this table. What can I use to capture this value while also making sure this value is accurate?
Based on my research, the recommended and safe way is to use OUTPUT clause because of its scope limited to SQL statement and it’s very forthright than other ways in SQL Server to get Identity value. It also returns correct Identity value of new inserted row in parallel environment.
Here is a T-SQL sample of getting the identity value of the new inserted row.
- CREATE TABLE[TableWithIdentityKey](ID INT IDENTITY(1, 1), Description VARCHAR(128))
- DECLARE @IdentityValue AS TABLE(ID INT);
- INSERT[TableWithIdentityKey](Description) OUTPUT Inserted.ID INTO @IdentityValue
- VALUES('Test Description')
- SELECT ID FROM @IdentityValue
- INSERT[TableWithIdentityKey](Description) OUTPUT Inserted.ID INTO @IdentityValue
- VALUES('Test Description 1'), ('Test Description 2')
- SELECT ID FROM @IdentityValue
Other similar way to get last inserted value into the IDENTITY column of a table are,
| Function |
Table |
Scope |
Session |
| @@IDENTITY |
Last identity value generated in any table |
Any Scope |
Current Session |
| SCOPE_IDENTITY() |
Last identity value generated in any table |
Current Scope |
Current Session |
| IDENT_CURRENT() |
Last identity value generated in specified table |
Any Scope |
Any Session |