SCOPE_IDENTITY vs. @@IDENTITY vs. IDENT_CURRENT in SQL Server 2008

I have seen several SQL MVP bloggers posting about an open bug in Microsoft Connect where SCOPE_IDENTITY does not always return the correct result when query parallelism is involved with SQL Server 2005 or SQL Server 2008.

SCOPE_IDENTITY, @@IDENTITY, and IDENT_CURRENT are all commonly used to retrieve an identity value for a row that has just been inserted into a table. I have seen many problems with people using @@IDENTITY (mainly when when DML triggers are being used) where the wrong result is returned.

I think that using IDENT_CURRENT is the safest of the three methods, since you have to specify the table that you are concerned with. The examples below show how all three of these behave.

-- Insert a row into TransactionHistory
INSERT INTO Production.TransactionHistory
     (ProductID,ReferenceOrderID,ReferenceOrderLineID,
      TransactionDate,TransactionType,Quantity,ActualCost,ModifiedDate)
     VALUES(326,4000,2,'9/24/2004', 'P', 3000, 0.23, GETDATE());

-- Insert a row into Product Category     
INSERT INTO Production.ProductCategory
     ([Name], rowguid, ModifiedDate)
     VALUES('Test Category', NEWID(), GETDATE())


-- Returns the last identity value generated for a specified table or view. 
-- The last identity value generated can be for any session and any scope. 
SELECT IDENT_CURRENT('Production.TransactionHistory') AS [IDENT_CURRENT];

-- Is a system function that returns the last-inserted identity value.
SELECT @@IDENTITY AS [@@INDENTITY];

-- Returns the last identity value inserted into an identity column in the same scope. 
-- A scope is a module: a stored procedure, trigger, function, or batch. 
-- Therefore, two statements are in the same scope if they are in the same stored procedure, 
-- function, or batch.
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

 

Technorati Tags:

About these ads
This entry was posted in SQL Server 2008. Bookmark the permalink.

12 Responses to SCOPE_IDENTITY vs. @@IDENTITY vs. IDENT_CURRENT in SQL Server 2008

  1. Jack says:

    In your comment above the IDENT_CURRENT statement you do note that it will return the lat identity value for that table for any session or scope which means that in a highly concurrent application you could get the identity value for another session. If you are using the identity values as I commonly do, to return the key to the calling application, this can cause unexpected and undesirable results, so you need to be careful. If this is why you are using one of the identity functions then you should use what MS, in the connect item, recommends, the OUTPUT clause, to return the identity value.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s