SQL Server SCOPE_IDENTITY() Function

In SQL Server, the SCOPE_IDENTITY() function is used to return the value of the most recently inserted identity column within the current scope. It only applies to cases where an identity column is used during data insertion. The SCOPE_IDENTITY() function returns a single value with a numeric data type. If no rows have been inserted within the current scope, it returns null.

Syntax

Here’s the syntax for the SCOPE_IDENTITY() function:

SCOPE_IDENTITY()

Usage

If you need to retrieve the value of a newly inserted auto-increment field within a single SQL statement, you can use the SCOPE_IDENTITY() function. Unlike the IDENT_CURRENT() function, the SCOPE_IDENTITY() function only returns the value of the most recently inserted auto-increment field within the current scope, rather than the current value of a specific table.

Using the SCOPE_IDENTITY() function can ensure that the correct identity column value is returned, even in high-concurrency situations.

Example

Suppose we have a Customers table with an auto-increment field, and we need to insert a row and return the value of the newly inserted auto-increment field. Here’s an example:

CREATE TABLE Customers (
  id INT IDENTITY(1,1) PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

INSERT INTO Customers (name, email)
VALUES ('John', '[email protected]');

SELECT  `SCOPE_IDENTITY()`  AS LatestId;

Result:

Suppose two users simultaneously insert data into the table. Here’s an example:

User 1 executes the following operation:

INSERT INTO Customers (name, email)
VALUES ('Amy', '[email protected]');

User 2 executes the following operation:

INSERT INTO Customers (name, email)
VALUES ('Bob', '[email protected]');

At this point, if User 1 uses the SCOPE_IDENTITY() function to retrieve the value of the most recently inserted auto-increment field, they will get the value of the auto-increment field for their own insert operation. Likewise, if User 2 uses the SCOPE_IDENTITY() function to retrieve the value of the most recently inserted auto-increment field, they will get the value of the auto-increment field for their own insert operation.

Conclusion

In SQL Server, the SCOPE_IDENTITY() function is used to return the value of the most recently inserted identity column within the current scope. It only applies to cases where an identity column is used during data insertion. Using the SCOPE_IDENTITY() function can ensure that the correct identity column value is returned, even in high-concurrency situations.