How to get the last generated identity column value in SQL Server - Part 8

From the previous session, we understood that identity column values are auto generated. There are several ways in sql server, to retrieve the last identity value that is generated. The most common way is to use SCOPE_IDENTITY() built in function.

Apart, from using SCOPE_IDENTITY(), you also have 
@@IDENTITY and IDENT_CURRENT('TableName') function.







Example queries for getting the last generated identity value
Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('tblPerson')

Let's now understand the difference between, these 3 approaches.


SCOPE_IDENTITY() returns the last identity value that is created in the same session (Connection) and in the same scope (in the same Stored procedure, function, trigger). Let's say, I have 2 tables tblPerson1 and tblPerson2, and I have a trigger on tblPerson1 table, which will insert a record into tblPerson2 table. Now, when you insert a record into tblPerson1 table,  SCOPE_IDENTITY() returns the idetentity value that is generated in tblPerson1 table, where as @@IDENTITY returns, the value that is generated in tblPerson2 table. So, @@IDENTITY returns the last identity value that is created in the same session without any consideration to the scope. IDENT_CURRENT('tblPerson') returns the last identity value created for a specific table across any session and any scope.

In brief:
SCOPE_IDENTITY() - returns the last identity value that is created in the same session and in the same scope.
@@IDENTITY - returns the last identity value that is created in the same session and across any scope.
IDENT_CURRENT('TableName') - returns the last identity value that is created for a specific table across any session and any scope.

9 comments:

  1. Hello,
    Can you please upload the Presentations of SQL Server. That would be really beneficial.
    Thank You

    ReplyDelete
  2. IF WE USE ANY OF THE ABOVE MENTIONED THREE FUNCTION BEFORE THE INSERT STATEMENT WAHT WILL BE THE O/P

    thanks
    wasim.add@gmail.com

    ReplyDelete
    Replies
    1. https://www.youtube.com/watch?v=n1iwngG_zNY&list=PL_nMO-wncU0nYz_BFwHJENd2YWoobA9Ly&index=8

      Delete
  3. Hello Wasim,
    If you execute SCOPE_IDENTITY() or @@IDENTITY for the first time in the current session before insert statement then you will get NULL.
    When it comes to IDENT_CURRENT('TABLENAME'), the result is same if no data is inserted by any of the sessions.

    ReplyDelete
  4. Hello Sir.
    When I insert record in table and get identity column that work fine.
    But problem is that when exception in program like(Primary Key)
    occurs then data not save in table but Identity column increment ?
    (in my program Identity column is not primary key )

    ReplyDelete
  5. Hi Jits Look at the below code.. the answer for ur question..
    When error occurs the Identity value is skipped ..
    So if u don't want to skip the Identity value use the below code..
    Here i have taken Email as Unique Key .. So when i insert same email id again i will get error number as 2627 for Unique key violation..

    Begin Transaction
    insert into tblPerson values('bobayya','hbc',2)
    if(@@Error = 2627)
    Begin
    Declare @Id int
    Set @Id = @@IDENTITY
    DBCC CheckIdent('tblPerson',reseed, @Id)
    end
    Else
    Begin
    Commit Transaction
    End

    In the above code when error occurs ,, i m just setting the Id to current Identity value .. so when u insert the row next time the Id value is not skipped.. it will continue consecutively...

    ReplyDelete
  6. I can't understand what these functions used for?
    can you please help me to understand

    ReplyDelete
  7. Scope stands for what purpose in this case ?

    ReplyDelete
  8. Thank you so much sir and much love from Afghanistan these videos and slides are so beneficial.

    ReplyDelete

It would be great if you can help share these free resources