Optimizing SQL Query for Select Max(Column) from ...

To get the maximum, minimum or other aggregate value value out of a table a simple query is written:

Select max(DatetimeId) from CounterData

but this query takes a very long time when it is executed against a table with 20 000 0000 +Rows.
Even though there is an index on the CounterData table with DateTimeId as the first sorted column.

Why that?

The query plan for this querie shows an Index Scan. This means every single row in the index is read and the max result is returned.

To optimize this simple query you just have to add a Where clause like this:

Select max(DateTimeId) from CounterData Where DateTimeId > 0

Now the query plan shows the usage of an Index seek wich is significantly faster. In this case the result is retunred in milli seconds.