Search code examples
sqlsql-serversql-server-2008

Safest way to get last record ID from a table

In SQL Server 2008 and higher what is the best/safest/most correct way

  1. to retrieve the ID (based on autoincrementing primary key) out of the database table?
  2. to retrieve the value of the last row of some other column (like, SELECT TOP 1 FROM Table ORDER BY DESC)?
Solution

  • Safest way will be to output or return the scope_identity() within the procedure inserting the row, and then retrieve the row based on that ID. Use of @@Identity is to be avoided since you can get the incorrect ID when triggers are in play.

    Any technique of asking for the maximum value / top 1 suffers a race condition where 2 people adding at the same time, would then get the same ID back when they looked for the highest ID.