Strange @@IDENTITY scope behaviour?

Question

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?

score 4 · Accepted Answer · answered May 17 '12 at 08:32

4

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.

answered May 17 '12 at 08:32

Damien_The_Unbeliever

234,701
27
340
448

Ahh sessions.. I see! Perfect explanation, thank you very much! However Do you know why SCOPE_IDENTITY would be returning NULL? – ingredient_15939 May 17 '12 at 08:37
`SCOPE_IDENTITY` will return `NULL` if there has been no `INSERT` to a table with an `IDENTITY` column in the current scope. – Damien_The_Unbeliever May 17 '12 at 08:39
Thanks Damien, but there was indeed an insert with an identity column. I mean, otherwise @@IDENTITY wouldn't return anything either. Am I missing something? – ingredient_15939 May 17 '12 at 08:42
It's just a plain `INSERT`, not wrapped inside e.g. a stored procedure? (The code within a stored proc is a different scope) – Damien_The_Unbeliever May 17 '12 at 09:22
Ah again.. yes, it wasn't in a stored proc, just a command object with the single sql statement. Didn't realise a sproc was treated as a different scope. Thanks! – ingredient_15939 May 17 '12 at 12:41

Romil Kumar Jain · Answer 2 · 2012-05-17T08:42:43.520

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.

Strange @@IDENTITY scope behaviour?

2 Answers2