IDENTITY property in SQL Server creates an identity type column. It generates auto-incrementing values in table by defining seed and increment values, works much like SEQUENCE object in SQL Server and Oracle. However, IDENTITY property is table dependent and SEQUENCE object works independently from the table.
Often we require to get last identity inserted value in SQL server. Multiple functions and methods are used to achieve this.
- OUTPUT Clause
This post will walk you through different ways to get last identity inserted value. Helping to use the most recommended practice, to prevent breaking database code in future.
Here are various examples to get last identity inserted value in SQL Server. First, let’s create a Student and Student_History table with ID as an IDENTITY column.
1 SCOPE_IDENTITY & @@IDENTITY Function in SQL Server
SCOPE_IDENTITY is most recommended function to get last identity inserted value in SQL Server. It will return a value on the same scope and same session.
Let’s do a simple insert on Student table and to get last identity inserted value.
Result is as expected. We can see the correct value “1” is returned by both the functions.
Let’s Create following trigger to understand the difference between SCOPE_IDENTITY and @@IDENTITY function in SQL Server.
In following query we’ve made an insert on Student table which activites a trigger TRG_INSERT_Student_History. To insert a test record in StudentHistory table having ID identity column.
In above example, even though the last Identity insert was done on StudentHistory table through a trigger, SCOPE_IDENTITY functions returns correct value i.e. “2” from Student table whereas @@IDENTITY function returns “1” from StudentHistory table considering any scope and same session.
Hence it’s recommended to use SCOPE_IDENTITY function to get last identity inserted value in SQL Server to avoid any conflicts of getting an identity value from any different scope or session.
2. IDENT_CURRENT(‘TableName’) Function in SQL Server
Use of IDENT_CURRENT function to get last identity inserted value of a specified table regardless of its scope or session. It may be potentially dangerous if we’ve issued an INSERT statement and trying to get last identity value generated by INSERT statement which might not be true in case some other session has run INSERT statement on the same table from another session thus it will consider later value. Use this cautiously.
3. OUTPUT Clause To Get Last Identity Inserted Value
Another recommended way to get identity value is OUTPUT clause. We may capture multiple identity values using OUTPUT clause which is not possible with SCOPE_IDENTITY function. Using this we can get last inserted values from INSERTED magic table.
Additional table variable is required to capture the values and later use them. We can’t use local variables to store OUTPUT clause values.
SCOPE_IDENTITY and OUTPUT clause is recommended way to get last identity inserted values in SQL Server.
You may read more useful post on SQL Server from tech-recipes.