'SELECT TOP' performance question

SQL Server builds different execution plans for TOP 100, using a different sort algorithm. Sometimes it's faster, sometimes it's slower.

For simpler examples of it, read How Much Can One Row Change A Query Plan? Part 1 and Part 2.

For in-depth technical details, plus an example of where the TOP 100 algorithm is actually slower, read Paul White's Sorting, Row Goals, and the TOP 100 Problem.

The bottom line: in your case, if you know that no rows will be returned, well...don't run the query, eh? The fastest query is the one you never make. However, if you need to do an existence check, just do IF EXISTS(stick query here), and then SQL Server will do an even different execution plan.

Looking at the two plans, you have a key lookup on both with dramatically different % costs. If you hover the mouse over the objects you will see the number of executions.

The key lookup is a lookup back to the clustered index as the index used in the index seek (top right) isn't covering all columns (select * so the clustered index must be used).

Top 100 is able to get the 100 rows needed in less reads from the index and then perform the lookup 100 times rather than for every row in the table. Also explains the increase in number of pages read when NOT doing the 'top'.