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

Question

Of the following two queries, which is faster?

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

I would say the first, because the second requires it to go through an aggregate function.

However, as marc_s said, test it.

Answer 2

You can't answer that without knowing at least something about your indexes (have you got one on customer_id, on order_date?), the amount of data in the table, the state of your statistics etc.etc.

Answer 3

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.

Answer 4

it depends on how big your table is. On the second query, i guess there's no need for "TOP 1," since MAX only returns one value. If I were you, I would use the second query.

Answer 5

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.