SQL Server IDENTITY() Function

In SQL Server, @@IDENTITY is a system variable used to return the identity column value of the most recently inserted row. It is returned after the insert operation and only returns the identity column value for the current session. If multiple rows are inserted, only the identity column value for the last inserted row is returned.

Syntax

The syntax for @@IDENTITY is very simple, just use it in a SQL query. It is shown below:

SELECT @@IDENTITY;

Use Cases

@@IDENTITY can be used in many different scenarios. Here are some common use cases:

  • Use @@IDENTITY when you need to retrieve the identity column value after inserting data.
  • If you are using a stored procedure to perform an insert operation and need to return the identity column value, you can store that value in an output parameter.

Examples

Here are two examples of using @@IDENTITY:

Example 1: Retrieve the Identity Column Value for the Most Recently Inserted Row

Assume we have a table named Employees that contains the following columns:

  • EmployeeID - Identity column
  • FirstName - First name
  • LastName - Last name
  • Title - Job title

We can use the following query to insert a new row into the table and use @@IDENTITY to return the identity column value for the most recently inserted row:

INSERT INTO Employees (FirstName, LastName, Title)
VALUES ('John', 'Doe', 'Manager');

SELECT @@IDENTITY;

After running the above query, @@IDENTITY will return the EmployeeID value for the new row.

Example 2: Use a Stored Procedure to Retrieve the Identity Column Value

Assume we have a stored procedure that inserts a new row into the Employees table and needs to return the identity column value for the new row. We can store that value in an output parameter like this:

CREATE PROCEDURE InsertEmployee
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @Title nvarchar(50),
    @EmployeeID int OUTPUT
AS
BEGIN
    INSERT INTO Employees (FirstName, LastName, Title)
    VALUES (@FirstName, @LastName, @Title);

    SET @EmployeeID = @@IDENTITY;
END;

Then, we can use the following query to call the stored procedure and check the output parameter to retrieve the identity column value for the new row:

DECLARE @NewEmployeeID int;

EXEC InsertEmployee 'Jane', 'Doe', 'Salesperson', @NewEmployeeID OUTPUT;

SELECT @NewEmployeeID;

After running the above query, @NewEmployeeID will contain the EmployeeID value for the new row.

Conclusion

In SQL Server, @@IDENTITY can help us easily retrieve the identity column value for the most recently inserted row and perform subsequent operations. However, it’s important to note that it only returns the identity column value for the current session and only returns the identity column value for the last inserted row.