@@ Identity Vs ScopeIdentity Vs Ident_Current in SQL

All of these are helpful in getting the last identity generated in a table. But before we start, we will discuss 2 points that will act as a base for the discussion.

• When we refer to a connection, we mean it to be in a different query window. Two different query windows mean two different connections.
  
  
• When we refer to a scope, we mean it to be either a Stored Procedure, trigger, function, or batch of statements.

For our discussion, we will be creating two sample tables, Table1 and Table2, with an auto-generated identity column. Identity values for Table1 will start from 1 and for Table2, it will start from 1001, so that we can differentiate among the ids generated in them. So let's start with their theoretical definitions first.

@@Identity: It will return the last identity value generated in a connection, regardless of the table in which it was generated or the scope of the Insert statement that generated the identity.

Scope_Identity(): It will return the last identity value generated in a connection, within the scope of the Insert query.

Ident_Current('Table_Name'): It will return the last identity value generated on the table specified, irrespective of the connection or scope of the insert statement.

For practical discussion, we will first create simple Insert queries on both of the tables and see how the identity values are being generated. We will be discussing various scenarios. These are described below.

Execute the queries in the same window:



Let's discuss the results generated from the previous queries.

1. @@Identity gives the last identity value generated, that in our case was 1001, in the current connection and current scope.
   
   
2. Scope_Idenity() was also 1001, since both of the statements were in the same connection and scope.
   
   
3. Ident_Current('Table_Name') returned the identity values generated in both of the tables since it is independent of the scope of the statements that generated the identity values.

Execute the queries in a different window: Now we execute the queries to get the identity values, for the same insert query above, in a new window.



Let's discuss the results generated from the previous queries.

1. @@Identity returns NULL since it was not only out of the scope of the Insert statement, but it also is in a new query window, that acts as a separate connection.
   
   
2. Scope_Identity also returns NULL, for the same reason as above.
   
   
3. Ident_Current('Table_Name') returned the identity values generated in both of the tables since it is independent of the scope of the statement that generated the identity values.

Scope_Identity vs @@Identity: In our first case we discussed above, we saw that both of the @@Identity and Scope_Identity were the same. This is because, both of the insert statements were in the same scope or batch. To differentiate between them clearly, we will discuss them with another example. We will create an Insert trigger on Table1 that will be executed for any record inserted in Table1.

This trigger will simply insert a new record in Table2. So our trigger will be like the following:



Next, we execute the insert query on Table1 and see the identity values generated.



In our preceding queries, when the insert statement on Table1 is executed, the trigger defined on it is also fired, that results in the insertion of a record in Table2. Now, since both of them were in a different scope but in the same connection, our results are different from the very first case that we discussed above.

1. @@Identity returns the last identity value generated in the current connection regardless of the scope of the Insert statement, that in this case is an identity generated in Table2, in other words value 1002.
   
   
2. Scope_Idenity() returns the identity generated in Table1, in the current connection and within the scope of the Insert statement, in other words the value 2.

The best use of these is in the Stored Procedures, where we might be required to get the id of the most recently created record. In such case, we need to carefully use these, we especially need to carefully decide whether we should use @@Identity or Scope_Identity.

I hope you enjoyed reading this.