Using @@IDENTITY and SCOPE_IDENTITY()

by ASH February 16, 2009 20:23

Often I see people just using @@IDENTITY when needing the latest used identity value inserted into a table.
However this is dangerous behavior seeing as a potential bug resides there.

As mentioned in the documentation for the functionalities @@IDENTITY returns the last used identity value within the given session, but the globally used identity. This means that if something else, within the same session (not to be mistaken with a HTTP session ;) ) uses identity insert, you risk getting the wrong value back.
SCOPE_IDENTITY() only returns within the same scope.
One exists called IDENT_CURRENT which can return the identity for any session for a given table. However, I’ll only focus on @@IDENTITY and SCOPE_IDENTITY() as they are most common.

An easy way to see the difference is to make two tables with an identity column and a dummy value field like this.

CREATE TABLE TableOne(
   
[ID] [int] IDENTITY(1,1
) NOT NULL,
   
[Value] [nvarchar](50
) NULL
)
CREATE TABLE TableTwo
(
   
[ID] [int] IDENTITY(10,1
) NOT NULL,
   
[Value] [nvarchar](50
) NULL
)
   

Note that the second table has a different initial identity value then TableOne. This will help with the illustration.
Now create a trigger on TableOne to run after Insert which inserts another row into TableTwo. Just some dummy data like this:

CREATE TRIGGER [dbo].[TriggerInsert]
   
ON  
[dbo].[TableOne]
   AFTER 
INSERT
AS 
BEGIN
   SET 
NOCOUNT ON

    
INSERT INTO TableTwo VALUES ('ss'
)
END
GO 
 
Now try running the following query to illustrate the difference between the two IDENTITY functionalities:
INSERT INTO TableOne VALUES ('test')
SELECT 
@@IDENTITY
SELECT SCOPE_IDENTITY
() 
 

You’ll notice that the first SELECT @@IDENTITY returns a different identity value then just inserted into TableOne, and that is because it takes the last used identity value for the given session. SCOPE_IDENTITY() returns the expected value.

This is a potential problem if not aware of the differences and for example triggers are used. It can often provide in foreign key integrity problems and can be difficult to debug if multiple developers work on the same database.

Comments

Add comment


 

  Country flag

biuquote
  • Comment
  • Preview
Loading



Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen | Modified by Mooglegiant

About me

My real name is Allan Svelmøe Hansen.

I live in Denmark, where I work as a developer for hedal:kruse:brohus using SQL Server and the .NET framework since 2004.  My primary fields of expertise is back end data integration, database design and optimization. But I also work with website development as well as application/services for server and SEO of websites.

Disclaimer

The opinions expressed herein are my own personal opinions and thoughts and does not represent my employer's view in any way, nor are my results guarentees for all situations.

Content is presented “as is”, with no warranty.