Top Vs Max

June 4, 2008 at 7:38 am (SQL Server 2005, SQL Server 2008, TSQL) (, , )


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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: