Scope_identity, ident_current, and @ identity

From http://www.yesky.com/imagesnew/software/tsql/ts_sa-ses_6n8p.htm

Scope_identity

Returns the last identity value inserted into the identity column in the same scope. 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, and @ identity are functionally similar, because they all return values inserted into 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 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, 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 the scope_identity () function is called when an insert statement occurs in the scope before the identifiers column, the function returns NULL.

For more information, see examples.

Example

The following example creates two tablesTZAndTy, AndTZCreate an insert trigger. When a row is inserted into a tableTZWhen, trigger (Ztrig) Will inspire andTyInsert a row.

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 ('elevator')

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 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 the same scope, which was the insert on table TZ*/

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

See

@ Identity