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

Scope_identity

Returns the last one inserted to the identity column in the same scope.
Identity value. A scope is a module-stored procedure, trigger, function, or batch processing. Therefore, if the two statements are in the same stored procedure, function, or batch processing, they are in the same scope.

 

Syntax

Scope_identity ()

 

Return type

SQL _variant

 

Note

Scope_identity, ident_current
Similar to @ identity, because they all return
Value in the identity 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.

 

Scope_identity and
@ Identity returns the last Identifier 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, and an insert trigger is defined on T1. When a row is inserted to T1, the trigger is triggered and a row is inserted to T2. This example illustrates two scopes: insert on T1 and insert on T2 as the trigger result.

 

Assume that both T1 and T2 have the identity column. @ identity and scope_identity return different values at the end of the insert Statement on T1.

 

@ Identity returns the value of the last identity column inserted to any scope in the current session. This value is the value inserted in T2.

 

Scope_identity () returns the identity value inserted in T1, which is the last insert that occurs in the same scope. If an insert statement occurs in the scope and is called before the identifier column
Scope_identity () function, the function returns a null value.

 

For more information, see examples.

 

Example

The following example creates two tables TZ and Ty, and creates an insert trigger on 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

Begin

Insert ty values
('')

End

 

/* Fire the trigger and find out what identity values you
Get

With the @ identity and scope_identity functions */

Insert TZ values ('rosalie ')

 

Select scope_identity () as [scope_identity]

Go

Select @ identity
As [@ identity]

Go

 

-- Here is the result set.

Scope_identity

4

/* Scope_identity returned the last identity value in
Same scope, which was the insert on table TZ */

 

@ Identity

115

/* @ Identity returned the last identity value inserted
Ty by the trigger, which fired due to an earlier insert on TZ */

References: online help documentation