Difference Between Scope_Identity(), Identity(), @@Identity, and Ident_Current()

What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?

The @@identity function returns the last identity created in the same session.
The scope_identity() function returns the last identity created in the same session and the same scope.
The ident_current(name) returns the last identity created for a specific table or view in any session.
The identity() function is not used to get an identity, it's used to create an identity in a select...into query.

The session is the database connection. The scope is the current query or the current stored procedure.

A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.

So, normally you would use the scope_identity() function.

What is the difference of Scope_Identity and @@Identity

Compare

@@IDENTITY

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.

SCOPE_IDENTITY

It returns the last identity value generated for any table in the current session and the current scope.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.

IDENT_CURRENT

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

SQL Server SCOPE_IDENTITY() vs @@IDENTITY

The only way scope_identity() could have the prior id value in this context is if the INSERT statement does not create any rows. In that situation, @@IDENTITY isn't gonna fix anything. In fact, @@IDENTITY is less specific, and therefore could only hope to make things worse.

What you can do is use a different variable for the second insert. Or, you could set @TheId back to NULL before the second insert runs. In this way, you'll be able to tell if something went wrong. @@rowcount is also useful for this.

I did see this in the comments:

"The second insert did not fail as the record was found in the database."

I put it to you perhaps the record was already in the database, before the code ran. Moreover, if there is a constraint on the table this could be the reason why the insert fails.

What is the difference between scope_identity() and current_identity()?

IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values.
However, the scope and session on which last is defined in each of these functions differ:
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Source

scope_identity vs ident_current

In that case you need to write the table name, what happens if you decide to change the table name? You then also must not forget to update your code to reflect that. I always use SCOPE_IDENTITY unless I need the ID from the insert that happens in a trigger then I will use @@IDENTITY

Also the bigger difference is that IDENT_CURRENT will give you the identity from another process that did the insert (in other words last generated identity value from any user)
so if you do an insert and then someone does an insert before you do a SELECT IDENT_CURRENT you will get that other person's identity value

See also 6 Different Ways To Get The Current Identity Value which has some code explaining what happens when you put triggers on the table

Difference among @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT

If you want to return the value you just inserted (to use to insert to child tables),then generally you need to use scope _identity(). It is specific to the scope of the statement your connection ran. It wil nto give you anyone else's identity value.

@@identiy, is also specific to your scope, but it also includes trigger valuies in the scope and thus if the table has a trigger that also inserts to an identity, that is the identity returned. as such, it means that @@identity should not be used to return teh value you inserted as it will start returnign the worng value as soon as anyone adds a trigger.

Then there is ident_current. This is the most dangerous of all because it retuns the last identitiy onteh table no matter which coneection put it in. So if you use that to get an identity value, you need to be awre that it is not necessarlity related to the record your connection put in and using this to get the identity to use to insert to child tables is a guarantee of data integrity problems.

Newer version of SQL server have an OUTPUT clause, this is far superior to using any of the three items above as you can return a set of identities and the values of other fields as well.

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT [sql server 2005]

I'm not entirely sure what these "knows bugs" in SCOPE_IDENTITY() should be. The only thing I'm currently aware of is here: Six reasons you should be nervous about parallelism mentioned as the very first point.

SCOPE_IDENTITY, @@IDENTITY and IDENT_CURRENT return null

Your "dbs" connection probably has some connection pooling.

Write SQL INSERT and then SELECT identity in the same SQL statement.
Best solution is to create a stored procedure to insert data and then return current identity.