Kendra Little digs into a tricky performance problem:
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.
I got an email recently about a case when SQL Server was not smart enough to do this with MAX() — but it was doing just fine with a TOP(1) ORDER BY DESC combo.
The question was: what’s the problem with this MAX?
It took me a while to figure it out, but I finally got to the bottom of the case of the slow MAX.
Great story and very interesting sleuthing work on Kendra’s part.