Scope_identity and @ identity

Scope_identity usage

Scope_identity and @ identity are used to obtain the last Identifier value returned from any table in the current session, simply put, after executing an insert statement, use the global variable @ identity to obtain the ID number of the insert record. However, the problem is that @ identity is global, therefore, its functions are reflected in all scopes, one operation, one trigger, and one stored procedure is called one scope. In this case, if multiple scopes occur, @ identity the ID number obtained is the result of the last scope. In this case, we will use the scope_identity method. Scope_identity
Only values inserted into the current scope are returned; @ identity is not limited to specific scopes.
Usage: Select scope_identity () as ID from [Table] Select @ identity as ID from [Table]
Instance:
SQL = "set nocount on; insert into [Table] (item) values ('" & item & "')" SQL = SQL &"; select @ identity as ID from [Table]; Set nocount off ;"

When inserting some auto-increment tables, you need to open them:
Syntax: Set identity_insert tablename on

---- Scope_identity and @ identity return 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. @ Identity is a global variable, while
Scope_identity is a variable you insert into the database currently ----
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

-- Running result.
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:
Y_id y_name
---------------
100 boathouse
105 rocks

110 elevator

 

/* Create a trigger to automatically add data to the Ty table when adding data to the TZ table */

 

Create trigger ztrig
On TZ
For insert
Begin
Insert ty values ('')
End

/* Call the trigger to increase cascade operations (similar to database cascade operations )*/

 

Insert TZ values ('rosalie ')

Select * From DBO. Ty
Select * From DBO. TZ

Select scope_identity () as [scope_identity]
Go
Select @ identity as [@ identity]
Go

Scope_identity

4

 

/* Scope_identity returns the ID added to the TZ table */

 

 

@ Identity

115

 

/* @ Identity returns (the last Identifier value generated in any table in the current session). This value is the identifier of the scope in which the trigger is inserted to the TZ table .*/

Be careful when using @ identity

Http://www.cnblogs.com/rijing2004/archive/2008/01/09/sqlserver_identity.html