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:
Comparison from SQL Query Plan Explorer:
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.