Thursday, August 22, 2013

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are function used for same value type but different results.
IDENT_CURRENT returns the current identity value irrespective of session and scope.
SCOPE_IDENTITY returns last identity value generated the current scope Irrespective of tables. I.e. it could be value from last table which is updated in current scope.
@@IDENTITY returns the last identity values that are generated in any table in the current session. It’s not limited to any specific scope. I.e. while updating in current scope the trigger on this table insert data in some other table
For example, there are two tables, Table1 and Table2, and an INSERT trigger is defined on Table1.
When a row is inserted to Table1, the trigger inserts a row in Table2. This scenario illustrates two scopes: the insert on Table1 and the insert on Table2 by the trigger.
Table1 and Table2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on Table1.
@@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in Table2.
SCOPE_IDENTITY () will return the IDENTITY value inserted in Table1.

No comments:

Post a Comment