In SQL Server, you can use the T-SQL IDENT_CURRENT() function to return the last identity value generated for a specified table or view on an identity column. The last identity value generated can be for any session and any scope.

Syntax

The syntax goes like this:

IDENT_CURRENT( 'table_or_view' )

The table_or_view argument is the name of the table or view whose identity value is returned.

Example 1 – Basic Usage

Here’s a basic code example.

SELECT IDENT_CURRENT('Pets') AS Result;

Result:

+----------+
| Result   |
|----------|
| 3        |
+----------+

In this case, the current identity value is 3.

Here’s another query that checks more tables.

SELECT 
  IDENT_CURRENT('Pets') AS Pets,
  IDENT_CURRENT('BestFriends') AS BestFriends,
  IDENT_CURRENT('Cities') AS Cities;

Result:

+--------+---------------+----------+
| Pets   | BestFriends   | Cities   |
|--------+---------------+----------|
| 3      | 121           | 180      |
+--------+---------------+----------+

Example 2 – Including the Schema

You can also include the schema in the argument.

SELECT IDENT_CURRENT('dbo.Pets') AS Result;

Result:

+----------+
| Result   |
|----------|
| 3        |
+----------+

Example 3 – After an Insert

In this example, I insert two more rows into the Pets table, then select IDENT_CURRENT() again.

INSERT INTO Pets (PetName)
VALUES ('Brian'), ('Alf');

SELECT IDENT_CURRENT('dbo.Pets') AS Result;

Result:

+----------+
| Result   |
|----------|
| 5        |
+----------+

Here’s what the table looks like:

SELECT * FROM Pets;

Result:

+---------+-----------+
| PetId   | PetName   |
|---------+-----------|
| 1       | Homer     |
| 2       | Felix     |
| 3       | Ruff      |
| 4       | Brian     |
| 5       | Alf       |
+---------+-----------+

The PetId column is the identity column. Its seed value is 1, and its increment value is also 1.

See How to Use the IDENTITY() Property in SQL Server for the code used to create this table.

Example 4 – Check All Tables in the Database

Here’s an example of checking all tables in the database for their identity seed value.

SELECT 
  TABLE_SCHEMA, 
  TABLE_NAME,   
  IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_CURRENT  
FROM INFORMATION_SCHEMA.TABLES  
WHERE IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL;

Result:

+----------------+----------------+-----------------+
| TABLE_SCHEMA   | TABLE_NAME     | IDENT_CURRENT   |
|----------------+----------------+-----------------|
| dbo            | ConstraintTest | 17              |
| dbo            | Event          | 1               |
| dbo            | Scoreboard     | 8               |
| dbo            | Pets           | 5               |
| dbo            | BestFriends    | 121             |
| dbo            | Cities         | 180             |
| dbo            | Colors         | 6               |
| dbo            | t6             | 1               |
| dbo            | t7             | 100             |
+----------------+----------------+-----------------+

About The Return Value

The return value for IDENT_CURRENT() is numeric(@@MAXPRECISION,0)). This means that it’s a numeric data type with a fixed precision and scale.

The precision depends on the server. The @@MAXPRECISION argument returns the level used by decimal and numeric data types as currently set in the server. The precision specifies the maximum total number of decimal digits (it includes those on the left and the right sides of the decimal point).

The second argument specifies a scale of 0, which means there are no decimal digits to the right of the decimal place.

You can run the following query to find the precision level currently set on your server:

SELECT @@MAX_PRECISION AS [Max Precision];

Here’s the result on my system:

+-----------------+
| Max Precision   |
|-----------------|
| 38              |
+-----------------+

Creating an Identity Column

You can create an identity column by using the IDENTITY() property in your CREATE TABLE or ALTER TABLE statement.

Another way to create an identity column is the IDENTITY() function. This enables you to create an identity column when using a SELECT INTO statement to transfer data from one source to another.

Differences with @@IDENTITY and SCOPE_IDENTITY()

The IDENT_CURRENT() function is similar to @@IDENTITY and SCOPE_IDENTITY(), but there are some differences.

@@IDENTITY and SCOPE_IDENTITY() return the last identity value generated in any table in the current session. However, the difference between these two functions is that SCOPE_IDENTITY returns the value only within the current scope, whereas @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT() on the other hand, is not limited by scope or session. Rather, it is limited to a specified table. It returns the identity value generated for a specific table in any session and any scope.

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.