The Case of MAX() Requiring an Index Scan, While TOP(1)/ORDER BY DESC Does Not
Category:
query-tuning
, sql-server
Most of the time in SQL Server, the MAX() function and a TOP(1) ORDER BY DESC will behave very similarly.
If you give them a rowstore index leading on the column in question, they’re generally smart enough to go to the correct end of the index, and – BOOP! – just pluck out the data you need without doing a big scan.