People who love creating SQL Server Table Primary keys with Identity Insert, they give control of managing primary key value (an auto generated value) to SQL Server. In this scenario, developer can not decide which number should be set in primary key column. Also, insert statement does not return the primary key value which is generated.
What we can do is, we have to ask SQL Server the lastly generated Primary Key value by the insert statement executed by us.
There are three ways Microsoft suggests us to get the Primary key value generated by Insert statement.Let us understand all three ways and then decide which one will be helpful in our case.
@@IDENTITY : It returns the last Identity value generated on a Connection in current session, regardless of Table and the scope of statement that produced the value. @@IDENTITY returns the last identity value generated in the current session, but it is not limited to the current scope. If you have a trigger on the table you are inserting a row and if trigger inserts a row in other table, you will get IDENTITY value of second insert statement executed by triggered because both executed in the same session.
SCOPE_IDENTITY(): It returns the last identity value generated by the insert statement in the current scope in the current connection regardless of the table. As like @@IDENTITY it returns the last identity of any table but explicitly in the current scope. Any identity generated by other triggers or procedures will not reflect it.
IDENT_CURRENT(‘TABLENAME’) : It returns the last identity value generated on the specified table regardless of Any connection, session or scope. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
Now it seems more difficult to decide which one will be exact match for my requirement. Lets see all 3 with example to make things clear.
Lets assume you have table named CustomerOrder, you have one trigger on insert of this table which inserts record in AlertEmailQueue table. This means when you get any order from customer an alert email will be queued.In this scenario, when you insert the row in CustomerOrder, trigger will also add a row in AlertEmailQueue. This execution happens under single session but different scopes. If you use Select @@IDENTITY after insert statement of CustomerOrder, you will get the identity value of AlertEmailQueue because this table got new row in the same session.
If you use select SCOPE_IDENTITY() along with CustomerOrder insert statement, you will get the exact result as per your expectation.
IDENT_CURRENT has the different use case. When you want to find the lastly inserted row’s primary key value by any user or any session, this is perfect solution.
See Also :