SQL Server – TOP vs ROW_NUMBER Performance Comparison

execution-planperformancequery-performancesql server

I'm learning about Execution plans and am trying out different queries and comparing their performance and stumbled upon this:

SELECT StatisticID
FROM (
    SELECT StatisticID, ROW_NUMBER() OVER (ORDER BY StatisticID) AS rn
    FROM FTCatalog.Statistic
    ) AS T
WHERE T.rn <= 1000
ORDER BY rn

SELECT TOP 1000 StatisticID
FROM FTCatalog.Statistic
ORDER BY StatisticID

They both return same result set – however first one executes faster and is less resource hungry (at least SSMS tells me that)
Here are execution plans:
Execution plans

Comparison from SQL Query Plan Explorer:
enter image description here
Could anyone give me some insight on what's actually happening behind the scenes and why results differ? If there's anything else you need – just let me know.

Thanks,
Evaldas.

Best Answer

I guess you are comparing the estimated costs for the queries. Those are just estimates based on (among other things) the estimated number of rows returned by the query. Not the actual number of rows.

Your first query estimated that it would return 30 rows and your second query estimated 1000 rows. That is where your difference in query cost comes from.

If you change the queries to fetch only 30 rows you will see that the estimated rows is the same for the queries and the first query actually is costed a bit higher, at least for me in SQL Server 2014.

Don't use the estimates when comparing performance of queries. Use things like duration, number of reads and size of memory grants instead.

Related Question