Vikram Lakhotia Blog on Asp.Net, Dot Net, SQL, VIsual Studio, C# etc

Hi,

When inserting a row in the database with an identity column as a primary key, most of the time we need to capture the new identity value generated. In SQL Server there cane as many as three approaches for the same.

            @@IDENTITY

            SCOPE_IDENTITY()

            IDENT_CURRENT(‘tablename’)

All of them can be used to find the last identity value inserted in the database, but they differ in the functionality depending on the scope or source of the insert as well as the connection that insert the row.

The server variable @@IDENTITY will return the last generated identity value accross all scope but for the same connection. The value returned will be for the last table inserted with identity column in the same connection. This means that if we insert some record in table (TableA) which has a trigger on the insert and the trigger inserts a record in some other table (TableB) with identity column then the @@IDENTITY will return the identity value inserted in TableB.

Function SCOPE_IDENTITY() is identical to @@IDENTITY with one exception. The value returned is limited to the current scope (i.e. the executed stored procedure). So in our previous example the value returned will the identity value inserted in TableA.

Finally, function IDENT_CURRENT spans all scope and all connections to retrieve the last generated table identity value. But the function is table specific and returns the value for the given table only.

Vikram