I finished a set of research and tests. I put it all together and gave it to Chuck Heintzleman at SQL Server Standard (that web site can use some help). Hopefully he likes it and publishes it. I understand that the upcoming issue will be on performance tuning. If he doesn’t like it, I’ll get it over to Peter Ward at SQL Server Performance or to Steve Jones at SQL Server Central.
I’m posting this because I see so many web searches looking for information comparing TOP to MAX.
Here are some short answers for those interested. Data to support these suggestions is included with the article. ROW_NUMBER works best with smaller data sets. TOP has more consistent operation over a range a data. MAX frequently resolves as a TOP operation, but sometimes as an aggregate and it costs more when it resolves as an aggregate. I would suggest a bias toward TOP or ROW_NUMBER, but test this in your own environment. These behaviours were consistent between 2005 and 2008. I didn’t test with 2000.