@@Identity vs ScopeIdentity vs Ident_Current


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 Table1 and 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 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 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:

@@Identity vs Scope_Identity() vs Identity_Current

Let’s discuss the results generated as per above queries.

  1. @@Identity gives the last identity value generated, which in our case was 1001, in the current connection and current scope.
  2. Scope_Idenity() was also 1001, as both the statements were in the same connection and scope.
  3. 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.

@@Identity vs Scope_Identity() vs Identity_Current

Let’s discuss the results generated as per above queries

  1. @@Identity returns NULL as it was not only out of scope of the Insert statement, but also in a new query window, which 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 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 @@Identity and 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:

@@Identity vs Scope_Identity() vs Identity_Current

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

@@Identity vs Scope_Identity() vs Identity_Current

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.

  1. @@Identity returns the last identity value generated in the current connection regardless of the scope of the Insert statement, which in this case is identity generated in Table2 i.e. value 1002.
  2. Scope_Idenity() returns the identity generated in Table1, in current connection and within the scope of the Insert statement,  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 @@Identity or Scope_Identity. Hope you enjoyed reading it.

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server. Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s