When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row. When the search criteria include more than one range, SQL Server instead grabs all the indexed values from both of the ranges and does a stream aggregate, which is far slower than performing a TOP() for each sub-value.
For example, assume a large number of orders per customer in a table like:
CREATE TABLE orders ( customer_id int, quantity int )
Running this query:
SELECT MAX(quantity) FROM orders WHERE customer_id IN (1,2)
will result in a query that takes several times as long as if only one customer ID were specified.
What is the most efficient way to perform a query like the above? Relatedly, if separate results were needed (i.e. GROUP BY customer_id), what would the best method be?
SQL Fiddle: http://sqlfiddle.com/#!3/ef0c6/1