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];