Click here to Skip to main content
Click here to Skip to main content

Interview Question: What are the Differences between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT()

, 14 Jul 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
How to get the last inserted identity value of an auto increment column of database tables using SQL Server variable (@@IDENITY) and functions (SCOPE_IDENTITY() and IDENT_CURRENT())

Introduction

In this tip, I explain how to get the last inserted identity value of an auto increment column of database tables using SQL Server variable (@@IDENITY) and functions (SCOPE_IDENTITY() and IDENT_CURRENT()). Both @@IDENTITY and SCOPE_IDENTITY() return the last identity value that is produced in a single session; in other words a connection while IDENT_CURRENT() returns the last identity value according to the table for any session.

@@IDENTITY

It returns the last identity value generated for a table in the current session. This table can be any table in the database. So @@IDENTITY has global scope in the database to get the last identity value; that value is generated for any table. It is limited for the current session but not limited for the current scope.

Suppose I have two tables UNIT and SUBUNIT. I have an INSERT trigger on the table UNIT that inserts a row in the table SUBUNIT. Now I insert a row in the table UNIT so my trigger will be fired and will insert a row in the table SUBUNIT also. Now I will get the @@IDENTITY value and it will be the SUBUNIT table's auto increment column last identity value instead of the UNIT table's last identity value.

SCOPE_IDENTITY()

It returns the last identity value generated for a particular table in the current session. It is not only limited to the current session but also limited to the current scope.

Suppose I have two tables UNIT and SUBUNIT. I have an INSERT trigger on the UNIT table that inserts a row in the UNIT table. Now I insert a row in the UNIT table so my trigger will be fired and will insert a row in the SUBUNIT table also. Now I will get the SCOPE_IDENTITY() value, then it will be the UNIT table's auto increment column last identity value instead of the SUBUNIT table's last identity value.

IDENT_CURRENT()

It returns the last identity value generated for any table that is passed as a parameter for it. It is neither limited to the current session nor to the current scope. But it is limited to a table, in other words, it depends on the table that is passed as the parameter. See:

 IDENT_CURRENT('Table_Name');

Using the Code

  • Create Two Tables

    I create two tables Table UNIT and Table SUBUNIT. I have a seed value different for each table so I can identify what value was obtained from @@IDENTITY and SCOPE_IDENTITY() and for which table.

    CREATE TABLE UNIT
    (
            Id INT IDENTITY(1,1),
            Name NVARCHAR(50)
    )
     
    CREATE TABLE SUBUNIT
        (
            Id INT IDENTITY(10,1),
            Name NVARCHAR(50)
      )
  • Create Trigger

    I create a trigger on Table UNIT that inserts a row in the SUBUNIT table.

    CREATE TRIGGER I_UNIT
    ON UNIT
    FOR INSERT
    AS
    INSERT INTO SUBUNIT(Name)
    VALUES ('gm')
  • Insert a row and get @@IDENTITY and SCOPE_IDENTITY()

    I insert a row in the UNIT table and get both of the @@IDENTITY and SCOPE_IDENTITY() values for the insert statement. See:

    INSERT INTO UNIT(Name)
    VALUES ('Kg')
     
    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]
    SELECT  IDENT_CURRENT('UNIT') AS [IDENT UNIT]
     
    SELECT @@IDENTITY AS [@@IDENTITY]
    SELECT  IDENT_CURRENT('SUBUNIT')AS [IDENT SUBUNIT]
  • OUTPUT

    The following figure shows that we got the @@IDENTITY value from the UNIT table while the SCOPE_IDENTITY() value was from the SUBUNIT table and IDENT_CURRENT() shows the value as a parameter that is passed as the table name.

    Output of query

    Figure 1.1 Output of query

Conclusion

Both @@IDENTITY and SCOPE_IDENTITY will the return last identity value in the current session but are different in their scope, but IDENT_CURRENT() does not depend on current session and current scope. I hope it will be helpful for you and if you have any doubt or feedback, then post your comments here or you can directly connect with me at https://twitter.com/ss_shekhawat.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sandeep Singh Shekhawat
Software Developer
India India
No Biography provided
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 PinmemberLydia Gabriella16-Jul-14 11:43 
GeneralRe: My vote of 5 PinprofessionalSandeep Singh Shekhawat16-Jul-14 17:20 
Generalneed to be aware of this PinprofessionalBrian A Stephens14-Jul-14 3:55 
GeneralRe: need to be aware of this PinmemberPatrick Harris16-Jul-14 5:19 
GeneralRe: need to be aware of this PinprofessionalSandeep Singh Shekhawat16-Jul-14 6:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150224.1 | Last Updated 14 Jul 2014
Article Copyright 2014 by Sandeep Singh Shekhawat
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid