On many occasions rows may be inserted utilizing an identity column as the primary key, in many of these cases it may be necessary to capture the newly auto-generated value. The following article will describe three such approaches:

• @@IDENTITY
• SCOPE_IDENTITY()
• IDENT_CURRENT()

Each of the previously mentioned approaches can be utilized to retrieve the last identity value inserted into a given database, but depending on your situation each derives its results differently depending on the scope, source of insert, and connection utilized.

One approach to retrieving the last identity value inserted is the server variable @@IDENTITY which returns the last generated identity value for the current connection.  Note that the result of this variable is the actual last inserted value for the current connection.  For example, if you execute a stored procedure against table [foo] which triggers an insert into table [bar] which also contains an identity column the value of the identity column in table [bar] is returned.

Another approach to retrieving the last identity value inserted is the function SCOPE_IDENTITY().  The function SCOPE_IDENTITY() is identical in nature to the server variable @@IDENTITY with one exception.  SCOPE_IDENTITY() returns a value limit to the current scope (stored procedure executed).  Utilizing the aforementioned example, if you execute a stored procedure against table [foo] which triggers an insert into table [bar] which also contains an identity column the value of the identity column in table [foo] is returned because the table [bar] is outside of the current scope.

The final approach to retrieving the last identity value inserted is the function IDENT_CURRENT() with spans every scope and all connections.  This function differs from the previous two methods of retrieval for two reasons. First it is table specific and returns only the values from the table specified and second it spans all connections.  The usage of this function is as follows: 

IDENT_CURRENT('TABLE_NAME')