In this article I would like to share my idea about getting Identity after a row was inserted in to the SQL Server 2005.
After inserting a row into the database which has primary key feild, most of the time we need the identity, We have three approches based on our reqiurements and situations.


All of the abouve three will get the identity value but in different approches.

The variable @@IDENTITY will return the last generated identity value produced on a connection, without based on the table that produced the value. While @@IDENTITY is limited to the current session, it is not limited to the current scope. This means that if we insert some record in Table1 which has a trigger on the insert and the trigger inserts a record in some other table2 then the @@IDENTITY will return the identity value inserted in Table2.

SCOPE_IDENTITY() will return the last IDENTITY value produced on a connection and by a statement in the same scope, without based on the table that produced the value. So we can say that this function is some identical to @@IDENTITY with one exception. 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 . So that means it will return identity value inserted in Table1.

Use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
IDENT_CURRENT will reutrn returns the last IDENTITY value produced in a table, Without based on the connection that created the value, and Without based on the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session., So it will retrieve the last generated table identity value.

Share it


Post a Comment