1. When there is a clustered index on the table and the column to be queried, both the MAX() operator and the query SELECT TOP 1 will have almost identical performance.
2. When there is no clustered index on the table and the column to be queried, the MAX() operator offers the better performance.

Reference: http://www.johnsansom.com/performance-comparison-of-select-top-1-verses-max/

Performance is generally similar, if your table is indexed.

Worth considering though: Top usually only makes sense if you're ordering your results (otherwise, top of what?)

Ordering a result requires more processing.

Min doesn't always require ordering. (Just depends, but often you don't need order by or group by, etc.)

In your two examples, I'd expect speed / x-plan to be very similar. You can always turn to your stats to make sure, but I doubt the difference would be significant.

They are different queries.

The first one returns many records (the biggest a_date for each event_id found within a_primary_key = 5 )

The second one returns one record (the smallest a_date found within a_primary_key = 5 ).

For the queries to have the same result you would need:

SELECT MAX(a_date) FROM a_table WHERE a_primary_key = 5

SELECT TOP 1 a_date FROM a_table WHERE a_primary_key = 5 ORDER BY a_date DESC

The best way to know which is faster is to check the query plan and do your benchmarks. There are many factors that would affect the speed, such as table/heap size, etc. And even different versions of the same database may be optimized to favor one query over the other.

I perform max and top on one table with 20,00,000+ records , and found that Top give faster result with order by than max or min function.

So , best way is to execute both your query one by one for some time and check connection elapsed time for than.

MAX and TOP function differently. Your first query will return the maximum value found for a_date that has a a_primary_key = 5 for each different event_id found. The second query will simply grab the first a_date with a a_primary_key = 5 found in the result set.

To add the otherwise brilliant responses noting that the queries do very different things indeed, I'd like to point out that the results will be very different if there are no rows matching the criteria in the select.

• SELECT MAX() will return one result with a NULL value
• SELECT TOP 1 will result zero results

These are very different things.

I ran an experiment and I got the Clustered Index Cost 98% when I used an aggregate like Min/ Max, but when I used TOP and Order By, Clustered Index Scan cost was reduced to 45%. When it comes to querying large datasets, TOP and Order By combination will be less expensive and will give faster results.