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.
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT(‘TableName’)
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.
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.
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT(‘TableName’)
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.
