Scope_identity ident_current @ identity

Scope_identity, ident_current, and @ identity are similar functions, because they all return values inserted into the ID column.


Ident_current is not restricted by the scope and session, but by the specified table. Ident_current returns the value generated for a specific table in any session and scope. For more information, see ident_current (TRANSACT-SQL ).

Scope_identity and @ identity return the last id value generated in any table in the current session. However, scope_identity only returns the value inserted into the current scope; @ identity is not limited to a specific scope.

For example, there are two tables t1 and t2 which are defined on T1 Insert Trigger. When a row is inserted into T1, the trigger is triggered and 2 Insert a row. This solution demonstrates two scopes: insert on T1 and insert on T 2 Insert a trigger.

Suppose t 1 And T2 both have the ID column, @ identity and scope_identity will be in T 1 The last return value of the insert Statement on. @ Identity returns the value of the last identifier column inserted in any scope of the current session. This is in T 2 . Scope_identity () returns the identity value inserted in T1. This is the last insert in the same scope. If the scope_identity () function is called before any insert statement acts on the identifier column in the scope, the function returns a null value.

If the statement and transaction fail, they will change the current identifier of the table, causing inconsistency of values in the ID column. Even if you have not committed a transaction that tries to insert a value to the table, you will never be able to roll back the id value. For example, if the insert statement fails due to an ignore_dup_key conflict, the current table id value will still increase.

Return type


Numeric


Example


Create two tables, TZ and Ty, and create an insert trigger in TZ. When a row is inserted into the table tz, the trigger (ztrig) is triggered and a row is inserted in ty.


Use tempdb
Go
Create Table TZ (
Z_id int identity ( 1 , 1 ) Primary key,
Z_name varchar ( 20 ) Not Null )
Insert TZ
Values ('lisa ')
Insert TZ
Values ('Mike ')
Insert TZ
Values ('carla ')
Select * From TZ
-- Result set: this is how table TZ looks.
Z_id z_name
-------------
1 Lisa
2 Mike
3 Carla
Create Table Ty (
Y_id int identity ( 100 , 5 ) Primary key,
Y_name varchar ( 20 ) Null )
Insert Ty (y_name)
Values ('boathouse ')
Insert Ty (y_name)
Values ('rocks ')
Insert Ty (y_name)
Values ('evator ')
Select * From Ty
-- Result set: this is how Ty looks:
Y_id y_name
---------------
100 Boathouse
105 Rocks
110 Elevator
/* Create the trigger that inserts a row in Table ty
When a row is inserted in Table TZ */
Create Trigger Ztrig
On TZ
For Insert As
Begin
Insert Ty values ('')
End
/* Fire the trigger and determine what identity values you obtain
With the @ identity and scope_identity functions .*/
Insert TZ values ('rosalie ')
Select Scope_identity () As [Scope_identity]
Go
Select @ Identity As [@ Identity]
Go


The following is the result set:


Scope_identity
4
/* Scope_identity returned the last identity value in the same scope. This was the insert on table tz .*/
@ Identity
115
/* @ Identity returned the last identity value inserted to Ty by the trigger. This fired because of an earlier insert on tz .*/