The faster of two SQL queries, sort and select top 1, or select MAX

Question

Which one is faster of the following two queries?

1

SELECT TOP 1 order_date
FROM         orders WITH (NOLOCK)
WHERE customer_id = 9999999
ORDER BY order_date DESC

2

SELECT MAX(order_date)
FROM         orders WITH (NOLOCK)
WHERE customer_id = 9999999

Answer 1

With an index on order_date, they are of same performance.

Without an index, MAX is a little bit faster, since it will use Stream Aggregation rather than Top N Sort.

Answer 2

ORDER BY is almost always slowest. The table's data must be sorted.

Aggregate functions shouldn't slow things down as much as a sort.

However, some aggregate functions use a sort as part of their implementation. So a particular set of tables in a particular database product must be tested experimentally to see which is faster -- for that set of data.