Query Exercise Answer: What Makes SELECT TOP 1 or SELECT MAX Different?


This Query Exercise was very different: I didn’t ask you to solve a particular problem. I pointed out that I’ve heard advice that SELECT MAX is faster than SELECT TOP 1, and that’s not quite true. I asked you to find factors that would cause these two queries to get different execution plans:

SELECT TOP 1 LastAccessDate FROM dbo.Users ORDER BY LastAccessDate DESC; SELECT MAX(LastAccessDate) FROM dbo.Users;

In the exercise post, I showed that with a nonclustered rowstore index like this, whose leading column is not LastAccessDate:

CREATE INDEX Location_LastAccessDate ON dbo.Users(Location, LastAccessDate);

That would give them different execution plans, leading to TOP 1