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

  1. 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

  1. INSERT INTO [dbo].[Customers]  
  2. ([CName]  
  3. ,[Phone])  
  4. VALUES  
  5. ('Bhanu Pratap',  
  6. '9628855006')  
  7. 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.
Ebook Download
View all
Learn
View all