In SQL Server, if you ever need to return the value created in an identity column, you have several options. Each of these options, although similar, do a slightly different thing.

In particular, you can use the following functions:

• IDENT_CURRENT() returns the last-inserted identity value for a given table.
• SCOPE_IDENTITY() returns the last identity value inserted into an identity column in any table in the current session and current scope.
• @@IDENTITY returns the last-inserted identity value in any table in the current session, regardless of scope.

Example

Here’s an example that demonstrates the difference between these three functions.

First, create two tables. Notice the different seed and increment values being used for the identity column in each table:

CREATE TABLE t1(id int IDENTITY(1,1));  
CREATE TABLE t2(id int IDENTITY(150,10));

Now create a trigger that inserts a row into the second table whenever a row is inserted into the first table:

CREATE TRIGGER t1_insert_trigger ON t1 FOR INSERT
AS
BEGIN
  INSERT t2 DEFAULT VALUES
END;

Triggers fire in a different scope, so that’s perfect for my example here.

Insert data into the first table, then select the results from both tables:

INSERT t1 DEFAULT VALUES;
SELECT id AS t1 FROM t1;
SELECT id AS t2 FROM t2;

Result:

+------+
| t1   |
|------|
| 1    |
+------+
(1 row affected)
+------+
| t2   |
|------|
| 150  |
+------+
(1 row affected)

So just to be clear, this data was inserted by two different scopes. The insert into t1 was done by the current scope. The insert into t2 was done by the trigger, which ran in a different scope.

Now let’s select from the functions previously mentioned:

SELECT 
  @@IDENTITY AS [@@IDENTITY],
  SCOPE_IDENTITY() AS [SCOPE_IDENTITY()],
  IDENT_CURRENT('t1') AS [IDENT_CURRENT('t1')],
  IDENT_CURRENT('t2') AS [IDENT_CURRENT('t2')];

Result:

+--------------+--------------------+-----------------------+-----------------------+
| @@IDENTITY   | SCOPE_IDENTITY()   | IDENT_CURRENT('t1')   | IDENT_CURRENT('t2')   |
|--------------+--------------------+-----------------------+-----------------------|
| 150          | 1                  | 1                     | 150                   |
+--------------+--------------------+-----------------------+-----------------------+

The result returned by @@IDENTITY is not limited to scope, and therefore it returns the last-inserted identity value, regardless of scope.

SCOPE_IDENTITY() returns the identity value from the first table, because that was the last inserted-identity value within the current scope (the trigger is outside the current scope).

The IDENT_CURRENT() function simply returns the last identity value inserted into the specified table, regardless of scope or session.

Open a New Session

Now, here’s what happens if I open a new session and run the previous statement again:

USE Test;
SELECT 
  @@IDENTITY AS [@@IDENTITY],
  SCOPE_IDENTITY() AS [SCOPE_IDENTITY()],
  IDENT_CURRENT('t1') AS [IDENT_CURRENT('t1')],
  IDENT_CURRENT('t2') AS [IDENT_CURRENT('t2')];

Result:

+--------------+--------------------+-----------------------+-----------------------+
| @@IDENTITY   | SCOPE_IDENTITY()   | IDENT_CURRENT('t1')   | IDENT_CURRENT('t2')   |
|--------------+--------------------+-----------------------+-----------------------|
| NULL         | NULL               | 1                     | 150                   |
+--------------+--------------------+-----------------------+-----------------------+

Both @@IDENTITY and SCOPE_IDENTITY() are NULL because they only return results from the current session. I haven’t performed any identity column inserts in this new session, so I get NULL.

IDENT_CURRENT() on the other hand, returns the same result as in the previous example, again because its results are based on the specified table, regardless of session or scope.