Difference between SCOPE_IDENTITY(), @@IDENTITY, and IDENT_CURRENT

 

原文：

http://www.go4coding.com/post/2011/06/16/Difference-between-Scope_Identity()Identity-and-Ident_Current.aspx

 

 

In this post, we will discuss on the difference among @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT().

@@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() are used to fetch the last inserted identity value. Most of the cases, we find that these functions return the same value indicating to an identity column. But these function are not really same, they show different behavior at different scenario.

Let's discuss now-
@@IDENTITY: It is a global variable that returns the last identity value inserted into any table in the current session, across any scopes.As @@IDENTITY is free of scope,so if an insert statement becomes the cause of insertion in some other table (In case of trigger),then @@IDENTITY will fetch the last inserted value in identity column of the table on which data was inserted in last.

SCOPE_IDENTITY: This function returns the last identity value inserted into any table in the current session and the current scope. So you will always get the value that was inserted in last by your insert statement in the identity column, regardless whether insertion happens with your insert statements in any table or you execute any procedure that is doing any insertion operation in any table.

IDENT_CURRENT: This function returns the last identity value generated (no matter it was inserted or not) for a specific table in any session and any scope. In simple words, IDENT_CURRENT() function gives the value that was at least generated as an identity for a specific table. I want to focus on the word “generated” instead of “inserted” because it is possible that the identity is generated by insert statement but could not be inserted due to run time error.

To show the difference among these function, we will create two tables with name Demo_A and Demo_B and then create an after-trigger on table Demo_A with name InsertionInDemoB.

CREATE TABLE Demo_A

(

id INT IDENTITY,

A_data VARCHAR(10)

)

go

CREATE TABLE Demo_B

(

id INT IDENTITY(2,2),

B_data VARCHAR(10)

)

go 

CREATE TRIGGER InsertionInDemoB ON Demo_A

FOR INSERT

AS

BEGIN

INSERT INTO Demo_B VALUES((SELECT A_data FROM inserted))

END

Run the following command and observe the result....

insert into Demo_A values('ABC')

select @@IDENTITY as [@@IDENTITY],SCOPE_IDENTITY() 

as [SCOPE_IDENTITY],IDENT_CURRENT('Demo_A') as [IDENT_CURRENT]

Why the @@IDENTITY and SCOPE_IDENTITY differ in result? The reason is that @@IDENTITY can not be limited with current scope.. so it is indicating the  Demo_B table because a insert command is also running on Demo_B after the execution of insert command on  Demo_A-table (because of trigger InsertionInDemoB). Now what is the Difference  between IDENT_CURRENT() and SCOPE_IDENTITY() functions, As we have already discuss that IDENT_CURRENT() is also free from the current scope limitation so by changing name of table in its argument, you can see the last generated identity value for the specify table. Here again I want to draw focus that IDENT_CURRENT() works on the identity value that is last generated for a table while @@IDENTITY and SCOPE_IDENTITY works on the identity value that are inserted in a table.Run the following command and observe the result....

BEGIN TRY

insert into Demo_B values('The statement will be terminated.')

END TRY

BEGIN CATCH

select @@IDENTITY as [@@IDENTITY],SCOPE_IDENTITY() as [SCOPE_IDENTITY],

IDENT_CURRENT('Demo_B') as [IDENT_CURRENT]

END CATCH

In the above example, Insert statement will cause an Exception, and so no value will be inserted in table Demo_B ,yet the identity will be generated,  and this identity value can be fetched by IDENT_CURRENT while @@IDENTITY and SCOPE_IDENTITY does not change their value in such a case.

You should always remember that SCOPE_IDENTITY or IDENTITY may produce inconsistent results in case of  parallel plan execution. For more detail, check the below link--

http://support.microsoft.com/kb/2019779

Thank you for reading.