In SQL Server, you can use the T-SQL SCOPE_IDENTITY() function to return the last identity value inserted into an identity column in the same scope.

A scope is a module (stored procedure, trigger, function, or batch). If two statements are in the same stored procedure, function, or batch, they are in the same scope.

Note that it returns the last identity value generated in any table in the current session. This is in contrast to the IDENT_CURRENT() function, which returns the last-inserted identity value for a given table, regardless of which session it’s in.

SCOPE_IDENTITY() is very similar to @@IDENTITY in that they both return the last-inserted identity value in the current session. The difference is that SCOPE_IDENTITY() is limited to the current scope, whereas @@IDENTITY is not limited to a specific scope.

Example 1 – Basic Usage

Here’s a basic code example of how it works.

SELECT SCOPE_IDENTITY() AS [Last-Inserted Identity Value];

Result:

+--------------------------------+
| Last-Inserted Identity Value   |
|--------------------------------|
| NULL                           |
+--------------------------------+

The reason for the NULL result is because I ran the statement immediately after opening new connection to SQL Server. The SCOPE_IDENTITY() function only returns results from the current session.

So in order to get a non-NULL result, I need to insert a value into an identity column.

Example 2 – Insert a Value for a Non-NULL Result

In this example, I create a table with an identity column. I then insert a default value into that table before selecting the contents of the table and then running SCOPE_IDENTITY() again.

CREATE TABLE scope_identity_test(id int IDENTITY(1,1));
INSERT scope_identity_test DEFAULT VALUES;
SELECT id FROM scope_identity_test;
SELECT SCOPE_IDENTITY() AS [Last-Inserted Identity Value];

Result:

+------+
| id   |
|------|
| 1    |
+------+
(1 row affected)
+--------------------------------+
| Last-Inserted Identity Value   |
|--------------------------------|
| 1                              |
+--------------------------------+
(1 row affected)

The table has one row and its identity column has a value of 1. This is the last-inserted identity value for the current session, and so SCOPE_IDENTITY() also returns 1.

Now if I add another row, the value increments accordingly:

INSERT scope_identity_test DEFAULT VALUES;
SELECT id FROM scope_identity_test;
SELECT SCOPE_IDENTITY() AS [Last-Inserted Identity Value];

Result:

+------+
| id   |
|------|
| 1    |
| 2    |
+------+
(2 rows affected)
+--------------------------------+
| Last-Inserted Identity Value   |
|--------------------------------|
| 2                              |
+--------------------------------+
(1 row affected)

Results From a New Session

As mentioned, SCOPE_IDENTITY() only returns results from the same session. This is also true of @@IDENTITY.

So if I open a new connection to SQL Server and run the previous SELECT statements again, I get the following results:

USE Test;
SELECT id FROM scope_identity_test;
SELECT SCOPE_IDENTITY() AS [Last-Inserted Identity Value];

Result:

+------+
| id   |
|------|
| 1    |
| 2    |
+------+
(2 rows affected)
+--------------------------------+
| Last-Inserted Identity Value   |
|--------------------------------|
| NULL                           |
+--------------------------------+
(1 row affected)

Now let’s insert a new row from within this new session:

INSERT scope_identity_test DEFAULT VALUES;
SELECT id FROM scope_identity_test;
SELECT SCOPE_IDENTITY() AS [Last-Inserted Identity Value];

Result:

+------+
| id   |
|------|
| 1    |
| 2    |
| 3    |
+------+
(1 row affected)
+--------------------------------+
| Last-Inserted Identity Value   |
|--------------------------------|
| 3                              |
+--------------------------------+
(3 rows affected)

So it caught up as soon as I inserted a new identity value.

However, let’s switch back to the original session and run the SELECT statements again (without inserting a new row):

SELECT id FROM scope_identity_test;
SELECT SCOPE_IDENTITY() AS [Last-Inserted Identity Value];

Result:

+------+
| id   |
|------|
| 1    |
| 2    |
| 3    |
+------+
(3 rows affected)
+--------------------------------+
| Last-Inserted Identity Value   |
|--------------------------------|
| 2                              |
+--------------------------------+
(1 row affected)

So the original session’s SCOPE_IDENTITY() results haven’t been affected by the second session.

Adding a Second Scope

The thing that differentiates SCOPE_IDENTITY() from @@IDENTITY, is that SCOPE_IDENTITY() is limited to the current scope.

For example, if the table has a trigger that inserts an identity value into another table, SCOPE_IDENTITY() would only report the first identity value. It would ignore the identity value for the second table, because that was created in a different scope @@IDENTITY on the other hand, would report the identity value for the second table (because it covers all scopes).

For an example of what I mean, see IDENT_CURRENT vs @@IDENTITY vs SCOPE_IDENTITY in SQL Server: What’s the Difference?

That article walks through a trigger example like what I’m talking about here.