Post
Ask Question

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.