Get max value for identity column without a table scan

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, righ开发者_StackOverflow社区t?

Can I look this up manually?

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

You can run this following statement and remove the last UNION ALL. Run this statement to get the current Identity values.

SELECT 
  ' SELECT '+char(39)+[name]+char(39)+' AS table_name, IDENT_CURRENT('+char(39)+[name]+char(39)+') AS currvalue UNION ALL'
  AS currentIdentity
FROM sys.all_objects WHERE type = 'U'

Is the Id the primary key or indexed? Seems like it should do a seek in those cases.

I'm pretty sure you could set up an index on that field in descending order and it would use that to find the largest key. It should be fast.