Do @@Identity And Scope_Identity Always Return The Record Just Added?

Question

Post reply

Do @@Identity And Scope_Identity Always Return The Record Just Added?

Phil Stratford

SSChasing Mays

Points: 641
More actions
June 11, 2014 at 5:53 pm

#292340

I understand the differences between @@IDENTITY and SCOPE_IDENTITY, I think, but I'm struggling to find out exactly how they're generated.
All the documentation tells me that these functions return the ID of the last record added to a table, but if I have a Stored Procedure containing an INSERT statement, and that procedure is part of a heavily-used database that could be getting executed by multiple users at the same time, if those two users both insert a record into the same table fractions of a second apart, is it possible that if I call @@IDENTITY or SCOPE_IDENTITY from the Stored Procedure right after the INSERT statement, they could actually return the ID of a record inserted by a different user?
I think the answer is that SCOPE_IDENTITY would avoid this because, as the name suggests, it gets the identity of the last record added from within the scope of the call to SCOPE_IDENTITY (in this case, from within the same Stored Procedure), but since I'm not entirely sure what the definition of the scope is, I don't know if I'm right in thinking this.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

You actually have 2 things to worry about, the connection and the scope.

@@IDENTITY

Returns the last IDENTITY from a connection, regardless of the table and regardless of the scope. If you have triggers where an IDENTITY is created, even if it is on another table, the last one created is what will be returned.

SCOPE_IDENTITY()

Returns the last identity value that was created in the current connection and the current scope. This eliminates any IDENTITY created by triggers.

As far as multiple users are concerned, putting a SCOPE_IDENTITY in a SP and using a BEGIN TRAN and COMMIT should help deal with concurrency issues.

I have always like this explanation:

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

If you are using @@IDENTITY in any kind of semi-complicated interaction between tables, you are asking for trouble.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

It is safer to use the IDENT_CURRENT function as it "returns the last identity value generated for a specific table in any session and any scope."

😎

Phil Stratford SSChasing Mays Points: 641 More actions · Answer 3

Actually, it seems like using IDENT_CURRENT is a terrible idea in the context of my question. As you've pointed out, it returns the last IDENTITY value produced for a specified table in any session and in any scope. Meaning that it would lead to exactly the kind of problem I was concerned about if multiple users were inserting into the same table at the same time. This is emphasised in the SQL Authority link that LinksUp provided.

Using SCOPE_IDENTITY is much safer and is exactly what I need, so thanks for the explanation and link, LinksUp.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Phil Stratford (6/12/2014)
Actually, it seems like using IDENT_CURRENT is a terrible idea in the context of my question. As you've pointed out, it returns the last IDENTITY value produced for a specified table in any session and in any scope. Meaning that it would lead to exactly the kind of problem I was concerned about if multiple users were inserting into the same table at the same time. This is emphasised in the SQL Authority link that LinksUp provided.
Using SCOPE_IDENTITY is much safer and is exactly what I need, so thanks for the explanation and link, LinksUp.

You are right, misread the question:hehe:

If the procedure is inserting into a table with an Identity column, non of the method mentioned should be used. The proper way is to use the OUTPUT clause of the INSERT statement!

😎