Difference between SQL @ Identity and Scope_identity ()

Such A business requirement is met. For example, Tables A and B. Table A have the identity column, and table B also has the identity column.

Use tempdb

 

Create table TM

(M_id int identity (1, 1) primary key

M_name varchar (100) not null

)

 

Insert into TM

Select 'A'

Union all

Select 'bb'

 

Create table TN

(N_id int identity (100,1) primary key

N_name varchar (100) not null

)

 

Create a trigger on the TM table:

Create trigger addTM

On TM

For insert

Begin

Insert TN values ('tai ')

End

 

----------------------------

When you run the following statement:

 

INSERT TZ
Select 'cc'

SELECT SCOPE_IDENTITY () AS [SCOPE_IDENTITY] -- the result is 1.
GO
SELECT @ identity as [@ IDENTITY] -- result 100
GO

 

As a scope, what is scope? (a module is a scope; a stored procedure, a trigger, a batch processing, or a function is an independent scope .).

Obviously SCOPE_IDENTITY () is in the TZ scope, while @ IDENTITY has no scope restriction. It is a global scope.

@ IDENTITY is the value of the auto-increment domain generated by the last operation of the global operation.

 

Scope_identity ()

Returns the last identity value generated for any table in the same scope of the current session.

@ Identity

Returns the last identity value generated by inserting the current session to the intent table (not limited by scope)