@@ Identity Vs ScopeIdentity Vs Ident_Current in SQL

Introduction

In this article, we will learn about @@ Identity Vs. ScopeIdentity Vs. Ident_Current in SQL Server. 

All of this help get 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 referring to scope, we mean a Stored Procedure, trigger, function, or batch of statements.

We will create two sample tables, Table1 and Table2, with an auto-generated identity column for our discussion. 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 begin with their theoretical definitions first.

@@Identity in SQL Server

@@Identity in SQL Server 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() in SQL Server

Scope_Identity() in SQL Server will return the last identity value generated in a connection within the scope of the Insert query.

Ident_Current('Table_Name') in SQL Server

Ident_Current('Table_Name') in SQL Server 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, which in our case was 1001, in the current connection and scope.
2. Scope_Idenity() was also 1001 since both statements had the same connection and scope.
3. Ident_Current('Table_Name') returned the identity values generated in both 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 also 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 tables since it is independent of the scope of the statement that generated the identity values.

Scope_Identity vs. @@Identity: In the first case we discussed above, we saw that both the @@Identity and Scope_Identity were the same. This is because both 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 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, which results in the insertion of a record in Table2. Now, since both were in a different scope but in the same connection, our results are different from the first case we discussed above.

1. @@Identity returns the last identity value generated in the current connection regardless of the scope of the Insert statement. In this case, it is an identity generated in Table2, 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.

Conclusion

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 cases, we need to use these carefully; we especially need to decide whether to use @@Identity or Scope_Identity in SQL Server.

Continue reading about the Differences Between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY in SQL Server here: Differences Between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY in SQL Server.

I hope you enjoyed reading this.