Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

@@Identity in Transactions

If I do the following:
- Connect to a SQL Server 2000 database (using SqlClient objects)
- Start a transaction
- INSERT a new row
- Run “SELECT @@Identity FROM [table]”

will I get a value specific to my transaction, or the whole table?


Joe Paradise
Thursday, December 2, 2004

Specific to your transaction, otherwise it wouldn't be much use.  It will only give the result you expect if there are no triggers defined: you might want to use SCOPE_IDENTITY or IDENT_CURRENT instead (look them up in SQL Server Books Online).

Thursday, December 2, 2004

That's what I thought, and that's the functionality I'm looking for (transaction-specific).  I'm not using any triggers, so they're not a factor.

Thanks for the confirmation.

Joe Paradise
Thursday, December 2, 2004

It is still worth considering using SCOPE_IDENTITY even if you are not currently using triggers:

You, or a future maintainer of the code, might start using triggers in the future without realising the impact it will have. It is also possible that a colleague will blindly copy your technique and not realise its limitations.

I'd go as far as to say you should always use SCOPE_IDENTITY in preference to the other two unless you need to support SQL Server 7.

Thursday, December 2, 2004

"SELECT @@Identity FROM [table]"

This will return one row for each row of the table, with the same value of @@IDENTITY for each row.  Do not use FROM with @@IDENTITY or the SCOPE_IDENTITY() function.

Tuesday, December 7, 2004

Another note:  If you are just getting the value from a function (like @@Identity) use 'Set' instead of Select.  It performs faster, and the compiler knows it doesn't have to create a cursor.

Second the motion for always using scope_identity.

Raymond Beckett
Thursday, December 30, 2004

*  Recent Topics

*  Fog Creek Home