I have a basic C# application that is trying to INSERT a row and get the ID. Really simple; there are no triggers, no stored procs or functions were involved, the app is single threaded, there is currently only one user. I have a really basic table with a INTEGER IDENTITY PK column. All very standard.
If I do the INSERT followed by a "SELECT @@IDENTITY" on the same connection, it works correctly.
If I use SCOPE_IDENTITY() instead, it returns NULL. I use SCOPE_IDENTITY on a variety of other occassions and it works fine. Why would this be? I thought SCOPE_IDENTITY() was the preferred replacement to @@IDENTITY.
I guess what I have is satisfactory but this was frustrating and I want to know why.
This is on SQL Server 2000 Standard edition with version SP3a + hot fixes
SCOPE_IDENTITY and @@IDENTITY will return last identity values 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.
This is the example given in BOL
Suppose if you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.
Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.
Yes, I read BOL and understand the documented theoretical and conceptual differences between the two. However, those differences don't apply to my situation.
There is only one thread, one process, one identity value, and one table involved. There are no triggers or functions or stored procs involved. It's a simple INSERT/get ID situation. And @@IDENTITY works and SCOPE_IDENTITY doesn't work which just doesn't make any sense according to what I've read.