SQL Scope_Identity

In SQL, the “scope_identity” function allows us to retrieve the last identity value in an identity column within the current scope.

In SQL, an identity column, also known as auto-increment or surrogate key column, is a special type of column that is automatically populated with unique numeric value as a new row is inserted into a table.

A common use of identity column is as a primary key to uniquely identify each row that is added into the new table.

In this tutorial, we will learn about the “scope_identity” function and look at various example usage.

SQL Server Scope_Identity

The “scope_identity” function is only supported in SQL Server database. The function returns the last identity value that is inserted within the current scope.

The following shows the basic syntax of the function:

SELECT SCOPE_IDENTITY();

The given query should return the last identity value that is inserted into the identity column. The resulting value is a numeric or decimal type.

Scope and Scope_Identity

Before we dive into the example usage of the scope_identity() function, let us talk about the scope.

The scope refers to the execution context in which a statement or batch is executed. In SQL Server, a scope can be a batch, stored procedure, trigger, or dynamic SQL batch.

For example, when we execute an “insert” statement directly in a batch or within a stored procedure, the scope_identity() function will then return the last identity value within that batch or procedure.

On the other hand, if we have multiple levels of nested triggers or procedures, the function will return the last identity value in the innermost scope where the INSERT occurred.

Example 1: Single Insert

Let us look at an example on how the function works when used in a single “insert” statement.

CREATE TABLE Employee (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
INSERT INTO Employee (FirstName, LastName)
VALUES ('Clark', 'Phew');
SELECT SCOPE_IDENTITY();

In this example, we retrieve the ID of the newly inserted record.

Example 2: Trigger

We can also use the “scope_identity” function to return the identity value in the scope of the trigger. An example is as follows:

CREATE TABLE Orders (
OrderID INT IDENTITY(1,1) PRIMARY KEY,
CustomerName VARCHAR(50),
OrderDate DATE
);
CREATE TRIGGER trg_LogOrder
ON Orders
AFTER INSERT
AS
BEGIN
DECLARE @OrderID INT;
SELECT @OrderID = SCOPE_IDENTITY();
INSERT INTO OrderLog (OrderID, LogTime)
VALUES (@OrderID, GETDATE());
END;
INSERT INTO Orders (CustomerName, OrderDate)
VALUES ('Alice', '2024-01-04');

In this case, we use the “scope_identity” function within a trigger to capture the OrderID of the inserted order.

Conclusion

In this post, we explored how to use the “scope_identity” function to retrieve the value of the last inserted row in the identity column.