SQL Server "TOP 101" much slower than "TOP 100" SQL Server shows huge decrease in performance when query is written like:

select top 101 name
from Dogs
order by name


compared to select top 100.

As far as I know, this happens for the reason that SQL Server in a top 100+ query simply orders all dataset and picks top records (while top 100 and less queries use more sophisticated algorithm).

Are there any workarounds for this?
&quot;As far as I know, this happens for the reason that SQL Server in a top
100+ query simply orders all dataset and picks top records (while top
100 and less queries use more sophisticated algorithm).&quot;

Your two queries likely have different execution plans. There's some hard-coding in the engine that says the top 100 will perform differently, but you might have stumbled into a problem like a spill into TempDB.
To find out for sure and get custom advice based on the queries you're facing, you can post the execution plans for download. You can use SQL Sentry Plan Explorer to anonymize them if necessary. Post the actual plans, not the estimated plans.