In this article, we will discuss about concept of
@@Identity, Scope_Identity() & Ident_Current('Table_Name') in SQL. All 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
Table2 with 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 between the id’s generated in them. So let’s start with their theoretical definitions first.
@@Identity: It will return last identity value generated in a connection, regardless of the table in which it was generated or 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
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 the tables and see how the identity values are getting generated. We will be discussing different scenarios. These are:
Execute the queries in the same window:
Let’s discuss the results generated as per above queries.
@@Identitygives the last identity value generated, which in our case was
1001, in the current connection and current scope.
1001,as both the statements were in the same connection and scope.
Ident_Current('Table_Name')returned the identity values generated in the both the tables as it is independent of the scope of the statements which generated the identity values.
Execute the queries in 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 as per above queries
NULLas it was not only out of scope of the
Insertstatement, but also in a new query window, which acts as a separate connection.
NULL, for the same reason as above.
Ident_Current('Table_Name')returned the identity values generated in the both the tables as it is independent of the scope of the statement which generated the identity values.
Scope_Identity vs @@Identity: In our first case we discussed above, we saw that both the
Scope_Identity were same. This is because, both 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, which 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 above queries, when insert statement on
Table1 is executed, the trigger defined on it is also fired, which results in insertion of record in
Table2. Now, as both of them were in different scope, but in same connection, our results are different from the very first case that we discussed above.
@@Identityreturns the last identity value generated in the current connection regardless of the scope of the
Insertstatement, which in this case is identity generated in
Scope_Idenity()returns the identity generated in
Table1, in current connection and within the scope of the
Insertstatement, i.e. 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 especially we need to carefully decide whether we should use
Scope_Identity. Hope you enjoyed reading it.