Get max value for identity column without a table scan

Question

I have a table with an Identity column Id.

When I execute:

 select max(Id) from Table

SQL Server does a table scan and stream aggregate.

My question is, why can it not simply look up the last value assigned to Id? It's an identity, so the information must be tracked, right?

Can I look this up manually?

Answer 1

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.

Answer 2

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)

Answer 3

Is the table clustered on that column? Can you use Top 1:

SELECT TOP 1 [ID]     
FROM [Table]
order by ID desc