« Home | Best Practices » | Resources to help jump-start your .NET Development... » | Agile Programming » | My First Blog Post »

SQL Server @@Identity

Most developers when writing an insert statement for a table that has an identity column will use the @@Identity function to return the value of the newly inserted column.

A better practice is to use the SCOPE_IDENTITY() function instead. The reason is that sometimes @@Identity may return back the wrong value. If you happen to have an INSERT trigger on the table that you are inserting data into that also happens inserts a row in a different table (auditing?) then @@Identity will return the identity value from the other table instead of the first table. SCOPE_IDENTITY() always returns the new identity value from the closest scoped operation.