Get max value for identity column without a table scan
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.
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)
Is the table clustered on that column?Can you use Top 1:
SELECT TOP 1 [ID] FROM [Table]order by ID desc