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

最新推荐文章于 2020-09-15 02:09:52 发布

cooleader320

最新推荐文章于 2020-09-15 02:09:52 发布

阅读量593

点赞数

分类专栏： MS Sqlserver 文章标签： table insert function command session exception

MS Sqlserver 专栏收录该内容

23 篇文章

订阅专栏

本文详细解释了SQL中IDENTITY, SCOPE_IDENTITY(),和IDENT_CURRENT()函数的区别，重点讨论了它们如何在不同场景下表现不同行为，以及在触发器作用下IDENTITY与SCOPE_IDENTITY之间的区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原文：

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.