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.