What is Scope_Identity() & Ident_Current() in Sql Server

SCOPE_IDENTITY(): It is useful when we want to know that during inserting of a record what is the current ID value of a row. Lets say we are inserting a record in Student table which contains a column StudentId which is auto generated. So, in this scenario if we want to know what will be the StudentId we can get it by using scope_identity(). It will return the last identity value created in the current session.

Query:

select scope_identity() //execute this after inserting the record.

IDENT_CURRENT(): 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.

• It is not limited by scope and session, it is limited to a specified table.
• It returns the identity value generated for a specific table in any session and any scope.

If we want to know the last inserted record in any table regardless of the scope then, execute following:
 

Query:

select IDENT_CURRENT('Student')as CurrentIdInserted

Note: scope_identity() or IDENT_CURRENT('tablename')will work only if the table has a column set with the property Identity Specification as Yes. e.g. RecordId