These are the most common functions to work with identity values in SQL Server. This blog looks at IDENT_CURRENT vs. SCOPE_IDENTITY vs. @@IDENTITY vs. IDENTITY comparison.

Let’s look at the definition first.

IDENT_CURRENT: It returns the last identity created for a particular table or view in any session. It is used as

Care should be taken when using this as it returns the value from any session not from the session where it is called from.

SCOPE_IDENTITY: It returns the last identity from a same session and the same scope. A scope is a stored procedure/trigger etc. It is called as

@@IDENTITY: It returns the last identity from the same session. It is used as

IDENTITY: This is used to insert an identity value in a table when using SELECT … INTO query. It is used as

Let’s now look at the demo. The below query creates a table and a stored procedure which inserts a record into the table and returns the identity values using the above function from the procedure scope and from outside the procedure scope.

Let’s now execute the procedure and analyze the values.

1_ident_current vs scope_identity vs identity in sql server

As shown above, all values from procedure and session scope return 3 except SCOPE_IDENTITY, which is 3 in procedure scope however it’s 2 in session scope. This is because of the insert statement executed earlier when preparing the table tblidentity.

A situation where @@IDENTITY and SCOPE_IDENTITY differ is when there is a trigger on a table which does an insert into a different table. In that case the @@IDENTITY returns the value from the trigger and not from the session unlike SCOPE_IDENTITY. An example of same is given below.

2_ident_current vs scope_identity vs identity in sql server

An After INSERT trigger on the table tblidentity inserts a value into tblident table. The @@IDENTITY returns the identity value from tblident table whereas the SCOPE_IDENTITY function returns the value from tblidentity table and from the current session and scope.



Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook