CODEWITHSUNDEEP
sqlmultithreadingidentity
Miguel
Miguel

Reputation: 3506

@@IDENTITY and Concurrence

Say I have a table TableA whose primary key is marked as IDENTITY, and that I execute the following two statements in a stored procedure

INSERT INTO TableA VALUES ('a', 'b', 'c', 'd')
SET @SomeVariable = @@IDENTITY

in order that @SomeVariable stores the primary key of the just-added value. If another client executes the same insert between the execution of the statements above, is it possible that @@IDENTITY and thus @SomeVariable stores the wrong (most recent) value?

Thank you very much.

Upvotes: 2

Views: 180

Answers (2)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364359

It really depends on what you mean by another client. @@IDENTITY returns the last identity value produced on a connection / session (regardless of table or scope of the command).

So if your clients do not share the connection the answer is no. If they share the connection (I don't think so because it will have many other problems) the answer is yes.

Upvotes: 0

gbn
gbn

Reputation: 432431

@@IDENTITY is for your connection only (not global) so you should get the last IDENTITY value that happened on your connection. However, this could be from a different scope, such as a trigger.

This is why SCOPE_IDENTITY() is best practice: it is for the scope with the INSERT and does not take into account triggers etc

For a truly global, non-concurrent and frankly idiotic solution it's IDENT_CURRENT. This will be affected by everyone who connect and writes to the table.

Upvotes: 5

Related Questions