Scope Identity in SQL Server

In this blog you will learn about Scope Identity in SQL Server.

Background

SCOPE_IDENTITY gets the last identity value inserted into a column that is a  defined identity in the same scope.

Scope may be a Stored Procedure, trigger, function or a batch statement.

The SCOPE_IDENTITY() function will return the most recent IDENTITY value inserted in the same scope (Stored Procedure, trigger, function or batch statement).

Note: It will return the NULL value if the function is invoked before any insert statements into an identity column in the scope.

Syntax

SCOPE_IDENTITY()

Return the type of the preceding function as a sql_variant.

Examples

SCOPE_IDENTITY() in select clause

 

Table

Customers

CId              CName             Phone

  6                   xyz              123456789

  9                   olp               965823658

INSERT INTO [dbo].[Customers]
([CName]
,[Phone])
VALUES
('Bhanu Pratap',
'9628855006')
SELECT SCOPE_IDENTITY()

Output

10

The preceding example returns the recently inserted identity values into the identity column named CId in the Customers table in the current session.