CODEWITHSUNDEEP
sql-serveridentity-column
ingredient_15939
ingredient_15939

Reputation: 3134

Strange @@IDENTITY scope behaviour?

Not a full bottle on this, but I had the impression @@IDENTITY gives the value of the last identity created in the database no matter where. SCOPE_IDENTITY() gives the value within the scope of the current statement.

I have a table with an identity column. No triggers, just a vanilla table and primary index. I've run this web app code in two separate browser tabs:

Dim connDb As New SqlConnection(myconnectionstring)
Dim transDb As SqlTransaction = connDb.BeginTransaction()
Dim cmdInsert As New SqlCommand("INSERT INTO mytable (somecolumn) VALUES (somevalue)", conn, transDb)
Dim cmdGetIdentity As New SqlCommand("SELECT @@IDENTITY", connDb, transDb)
' Add the record, pause, get identity.
Dim iNewHosp As Int32 = cmdInsert.ExecuteNonQuery() ' Returns 1 correctly.
Threading.Thread.Sleep(5000) ' Give the other tab time to add a record.
Dim iNewID As Int32 = cmdGetIdentity.ExecuteScalar ' Returns the new ID.
' Commit trans, close, dispose, etc..

Note this is all done within a transaction, not sure if that makes a difference. Using SQL Server 2000 here (unfortunately). Tried in SQL2008 as well, same thing happens.

So I run the page in one browser tab then, while it's sleeping, run it again in the other tab, which adds another record. The "problem" I'm seeing is that the first tab then returns the correct ID for its INSERT, as does the other tab. I thought the first tab would return the ID of the most recent insert, ie. the one done in the second tab?

I originally tried using SCOPE_IDENTITY() for this, but it was returning NULL, hence I tried @@IDENTITY just to see what was going on. So I have 2 questions - why would SCOPE_ return NULL, and why does @@IDENTITY return a scope-specific result?

Upvotes: 2

Views: 244

Answers (2)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

You are doing Insertion of Records in Transacation , so SCOPE_IDENTITY is limited to that transacation. So you are not able to see value inserted from other transaction.

You will be able to see the identity inserted by second tab when the transaction is commited for the second tab.

SELECT SCOPE_IDENTITY()

It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

No, @@IDENTITY is still restricted to identity values generated within the same session. See the discussion in SCOPE_IDENTITY:

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

(Emphasis added)

Scope ≠ Session. Here, scope is referring to e.g. that @@IDENTITY would return an identity value generated by an INSERT within a trigger. As you should also see, your final option is IDENT_CURRENT which lets you query for the most recent identity value in a specific table, regardless of session.

Upvotes: 4

Related Questions