In SQL Server, you can use the T-SQL @@IDENTITY system function to return the last-inserted identity value in the current session.

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.

The SCOPE_IDENTITY() function is very similar to @@IDENTITY in that it also returns the last-inserted identity value in the current session. The difference is that SCOPE_IDENTITY() is limited to the current scope.

Example 1

Here’s a basic code example of @@IDENTITY usage.

SELECT @@IDENTITY AS [Last-Inserted Identity Value];

Result:

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

The reason this returns NULL is because I opened a new session to SQL Server, and I haven’t yet updated an identity column during my current session.

Below is some code that does some identity column updates.

CREATE TABLE Cats(id int IDENTITY);
CREATE TABLE Dogs(id int IDENTITY);

INSERT Cats DEFAULT VALUES;
INSERT Cats DEFAULT VALUES;
SELECT @@IDENTITY AS [Last-Inserted Identity Value];

Result:

+--------------------------------+
| Last-Inserted Identity Value   |
|--------------------------------|
| 2                              |
+--------------------------------+

Here, I create a couple of tables, insert some data, then select the current identity value.

The current identity value is 2 because I inserted two rows into that table.

Now let’s insert one row into the other table:

INSERT Dogs DEFAULT VALUES;
SELECT @@IDENTITY AS [Last-Inserted Identity Value];

Result:

+--------------------------------+
| Last-Inserted Identity Value   |
|--------------------------------|
| 1                              |
+--------------------------------+

The returned value is 1, because that’s the last-inserted identity value for this session.

Example 2 – Compared to IDENT_CURRENT()

Here it is compared to IDENT_CURRENT().

SELECT 
  @@IDENTITY AS [@@IDENTITY],
  IDENT_CURRENT('Cats') AS [IDENT_CURRENT('Cats')],
  IDENT_CURRENT('Dogs') AS [IDENT_CURRENT('Dogs')];

Result:

+--------------+-------------------------+-------------------------+
| @@IDENTITY   | IDENT_CURRENT('Cats')   | IDENT_CURRENT('Dogs')   |
|--------------+-------------------------+-------------------------|
| 1            | 2                       | 1                       |
+--------------+-------------------------+-------------------------+

As mentioned, IDENT_CURRENT() returns its result based on the specified table. Therefore we can use it to find the last identity values for each table.

Example 3 – Switch to a New Session

Now if I open up a new connection and select @@IDENTITY again, this is what happens:

USE Test;
SELECT 
  @@IDENTITY AS [@@IDENTITY],
  IDENT_CURRENT('Cats') AS [IDENT_CURRENT('Cats')],
  IDENT_CURRENT('Dogs') AS [IDENT_CURRENT('Dogs')];

Result:

+--------------+-------------------------+-------------------------+
| @@IDENTITY   | IDENT_CURRENT('Cats')   | IDENT_CURRENT('Dogs')   |
|--------------+-------------------------+-------------------------|
| NULL         | 2                       | 1                       |
+--------------+-------------------------+-------------------------+

The @@IDENTITY result is NULL because I haven’t inserted anything into an identity column in the new session.

The IDENT_CURRENT() results are not NULL, because its result is based on the table – not the session.

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT()

See IDENT_CURRENT vs @@IDENTITY vs SCOPE_IDENTITY in SQL Server: What’s the Difference? for a simple example that walks through the differences between these three functions.