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.
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.