SQL Server IDENT_CURRENT() Function

IDENT_CURRENT() is a function in SQL Server used to retrieve the last inserted identifier for a specific table. It returns the current value of the last inserted identifier for the specified table. This function is useful for automatically generating identifiers when inserting data into a table.

Syntax

Here is the syntax for the IDENT_CURRENT() function:

IDENT_CURRENT (table_name)

Parameter explanation:

table_name: Required. The name of the table to be queried.

Use Cases

The IDENT_CURRENT() function is useful in many scenarios. For example, in data processing, it can be used to retrieve the current value of the last inserted identifier for a table to achieve the functionality of automatically generating identifiers. At the same time, the function can also be used to query the value of the last inserted identifier for existing data in a table.

Examples

Here are two examples of using the IDENT_CURRENT() function:

Example 1: Retrieving the current value of the last inserted identifier for a table

Assuming there is a table named Person that contains an automatically generated identifier column ID, and we need to retrieve the current value of the last inserted identifier for the table. We can use the IDENT_CURRENT() function to achieve this. Here is the complete example code:

CREATE TABLE Person
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

INSERT INTO Person (Name)
VALUES
('Tom Smith'),
('Mary Johnson'),
('David Lee'),
('Alex Chen');

SELECT IDENT_CURRENT('Person') AS LastID;

In this example, we first create a table named Person and add some sample data to it. Then, we use the IDENT_CURRENT() function to retrieve the current value of the last inserted identifier for the Person table. Finally, we get the current value of the last inserted identifier from the query result.

After executing the above code, the following result is obtained:

LastID
------
4

Example 2: Querying the value of the last inserted identifier for existing data in a table

Assuming there is a table named Person that contains an automatically generated identifier column ID, and we need to query the value of the last inserted identifier for existing data in the Person table. We can use the IDENT_CURRENT() function to achieve this.

Create table:

CREATE TABLE Person
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

Add sample data:

INSERT INTO Person (Name)
VALUES
('Tom Smith'),
('Mary Johnson'),
('David Lee'),
('Alex Chen');

Get the last identity value:

SELECT IDENT_CURRENT('Person') AS LastID;

Insert two more rows of sample data:

INSERT INTO Person (Name)
VALUES ('Jerry Green'), ('Amy White');

Get the updated last identity value:

SELECT IDENT_CURRENT('Person') AS LastID;

In this example, we first create a table named Person and add some sample data to it. Then, we use the IDENT_CURRENT() function to retrieve the value of the last inserted identifier for existing data in the Person table. We get the current value of the last inserted identifier from the query result. Finally, we add some new data to the Person table and use the IDENT_CURRENT() function again to retrieve the updated current value of the last inserted identifier.