Get max value for identity column without a table scan

What if you have deleted the latest record?

The value of IDENTITY would not correspond to the actual data anymore.

If you want fast lookups for MAX(id), you should create an index on it (or probably declare it a PRIMARY KEY)

You can use IDENT_CURRENT to look up the last identity value to be inserted, e.g.

IDENT_CURRENT('MyTable')

However, be cautious when using this function. A failed transaction can still increment this value, and, as Quassnoi states, this row might have been deleted.

It's likely that it does a table scan because it can't guarantee that the last identity value is the MAX value. For example the identity might not be a simple incrementing integer. You could be using a decrementing integer as your identity.