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.