In SQL server the are three ways to get the latest value of an Identity Record
- SELECT @@IDENTITY
- SELECT IDENT_CURRENT(‘tablename’)
- SELECT SCOPE_IDENTITY()
SELECT @@IDENTITY
This will always return the last IDENTITY value produced on a given connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. While @@IDENTITY is limited to the current session, it is not limited to the current scope. For example if a trigger is added to a table to do auditing and that audit table has a Identity Column the value of the @@IDENTITY will contain the identity value for the trigger table and not the table within the codes scope. This is because the the trigger insert fired after the table insert.
SELECT IDENT_CURRENT(‘tablename’)
This statement will return the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. This is very seldom used as IDENT_CURRENT is only limited to a specified table and can cause havoc in a multi user environment if relied on. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
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. like @@IDENTITY it will will return the last identity value created in the current session, however the but the SCOPE_IDENTITY() will also be limit it to your current scope. When inserting into a table it will only return the identity for that table and will not return and other inserts any triggers could have caused. This is typically what is expected when authoring code.
So when considering @@IDENTITY vs SCOPE_IDENTITY() always go with SCOPE_IDENTITY(). Using @@IDENTITY is opening up potential problems when triggers which involve identity columns are subsequently added to tables. Using SCOPE_IDENTITY() will always return the the expected value in the context of the last insert statement. and will protect your code from any triggers added at a later date.