Safest way to get last record ID from a table

Question

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)?

Answer 1

SELECT IDENT_CURRENT('Table') 

You can use one of these examples:

SELECT * FROM Table  WHERE ID = (     SELECT IDENT_CURRENT('Table'))  SELECT * FROM Table WHERE ID = (     SELECT MAX(ID) FROM Table)  SELECT TOP 1 * FROM Table ORDER BY ID DESC 

But the first one will be more efficient because no index scan is needed (if you have index on Id column).

The second one solution is equivalent to the third (both of them need to scan table to get max id).