SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

Solarwinds

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer
Next Post
SQL SERVER – Fix : Error 701 There is insufficient system memory to run this query

Related Posts

229 Comments. Leave new

  • Hi, I have a concern using IDENT_CURRENT. I just created a table as:

    CREATE TABLE [dbo].[Tbl1](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [LastName] [nvarchar](50) NULL
    ) ON [PRIMARY]

    I did not insert a single record. The table is kept blank. Now when I fired below SQL:

    SELECT IDENT_CURRENT(‘TBL1’) IDENTCURRENT

    I get the output as:

    IDENTCURRENT
    1

    Why is this happening? Is this some kind of bug in SQL Server? Can you please explain me this scenario?

    Regards,
    Tarun

    Reply
  • pendrives baratos personalizados
    July 7, 2014 2:17 pm

    I think that what you wrote made a great deal of sense.
    However, what about this? what if you added a little content?

    I am not saying your content isn’t solid., but what
    if you added a post title to maybe grab people’s attention? I mean SQL SERVER
    – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record | Journey
    to SQL Authority with Pinal Dave is kinda boring.

    You could glance at Yahoo’s home page and watch how they write post titles to get viewers interested.
    You might try adding a video or a pic or two to grab readers
    interested about what you’ve got to say. Just my opinion, it could bring
    your posts a little livelier.

    Reply
  • Hy sir .. i making an LMS system , in this i want courses to show by semester wise through inner join query because the courses table is different and that table is connected to semester table through foreign key.
    so for this i created two the first store procedure one procedure is below here.
    ALTER proc [dbo].[Insert_Semister_Type]
    @S_typeName varchar(50)
    as
    insert into Semister_Type (SemisterName) values (@S_typeName)

    return

    and the second is here..
    ALTER proc [dbo].[A_Update_Courses_By_Semister_Wise_For_Examination]
    as
    SELECT 0 as Of_C_ID,’—- Please Select —-‘ as CourseName
    FROM Offered_Courses_Sylabus INNER JOIN
    Ad_Teacher_In_Oferd_Course ON Offered_Courses_Sylabus.Of_C_ID = Ad_Teacher_In_Oferd_Course.Of_C_ID INNER JOIN
    Program_Sylabus ON Offered_Courses_Sylabus.P_S_ID = Program_Sylabus.P_S_ID INNER JOIN
    Courses ON Program_Sylabus.Course_id = Courses.Course_id
    union
    SELECT Ad_Teacher_In_Oferd_Course.Of_C_ID, Courses.CourseName
    FROM Offered_Courses_Sylabus INNER JOIN
    Ad_Teacher_In_Oferd_Course ON Offered_Courses_Sylabus.Of_C_ID = Ad_Teacher_In_Oferd_Course.Of_C_ID INNER JOIN
    Program_Sylabus ON Offered_Courses_Sylabus.P_S_ID = Program_Sylabus.P_S_ID INNER JOIN
    Courses ON Program_Sylabus.Course_id = Courses.Course_id
    — THIS SEMISTER ID WILL ALWAYS BE CHANGE THERE ACORDING TO CURRENT SEMISTER —
    where Semister_ID=1

    return

    here i am giving the semester value by default , what i want , i want that, when i insert the first new semester by store procedure 1 so that value should automatically assign to that 2nd procedure in which i am giving the default value.. i knew only that ,that will be done by @@identity but i don’t know how to do it.. please help me i am waiting for your response . thank you

    Reply
  • THANK YOU! Great article as usual. I like the way you write and keep it short, sweet and accurate. Gaz

    Reply
  • some time ago, looking in internet I found this workaround that is working for me, but need to be done each time a database is published to update the triggers.

    edit all the insert triggers in a database that is published, and execute for update them.
    the change should be distributed to all servers.

    /**********************************************************/
    BEGIN TRIGGER
    /**********************************************************/

    DECLARE @identity int
    DECLARE @strsql varchar(128)
    set @identity=@@IDENTITY

    /**********************************************************/
    TRIGGER
    /**********************************************************/

    set @strsql=’select identity(int,’+CAST(@identity as varchar(15)) +’,1) as id into #temp’
    exec(@strsql)
    if @@error 0
    goto FAILURE

    return

    /**********************************************************/
    END TRIGGER
    /**********************************************************/

    I understand that at the begin of the trigger ths identity is stored, the trigger does the operations needed for distribution or whatever else, and at the end restores the original identity vale.

    Reply
  • Hi Pinal,

    I need to know the difference between T SQL features on 2008 vs 2012 and CDC SQL SERVER 2008 R2 and CDC SQL SERVER 2012. Can you please any one help me out on this.

    Reply
  • Thank you sir for your solution it works fine for me

    Reply
  • Hello Sir
    when a Identity value is generated than where last identity value saved.

    Reply
  • thank you, this saved my bug… :)

    Reply
  • Hello Sir,
    Please I have a table (marks) with about 586065 rows; when you try to insert records of about 1,200 rows into the table (marks) it takes about 140 seconds to complete insertion.
    When you try to select from the table (marks), it takes about 10 seconds to fetch records.
    Is there a way to minimize the time taken to insert or search significantly?

    I’m using stored procedure in SQL Server 2008 r2
    below is the stored procedure:

    [my_DB].[Marks_INSERT]
    (
    @cExam AS varchar(50) = null,
    @studID AS varchar(50) = null,
    @semID AS varchar(10) = null,
    @examID AS varchar(5) = null,
    @dateTaken AS smalldatetime = null,
    @marks AS float = null
    )
    AS

    INSERT INTO
    [my_DB].[Marks]
    (
    [CExam],
    [StudID],
    [SemID],
    [ExamID],
    [DateTaken],
    [Marks]
    )
    VALUES
    (
    @cExam,
    @studID,
    @semID,
    @examID,
    @dateTaken,
    @marks
    )

    SELECT SCOPE_IDENTITY()

    Reply
  • I always had a doubt about whether these functions were “user session” specific. Thanks to you sir, now my doubt is all gone.
    A very crisp and to the point article. Thanks

    Reply
  • 123456@gmail.com
    December 5, 2016 4:44 pm

    i need without identity

    Reply
  • SELECT *
    FROM foo
    WHERE ID = (SELECT MAX(ID) FROM foo)

    Reply

Leave a Reply

Menu