asked on

why ROW_NUMBER() don't use tempdb space

Dear all,

Right now I read this to learn how to improve query:

http://www.sql-server-performance.com/2013/how-to-optimize-sql-server-query-plan/

and I can't see why ROW_NUMBER() can improve Table Spool operation, any idea ?

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

"btw, in my opinion this is a bad article. It contains some minor problems.

I think so and it is trying to sales the dbforgo studio.

so why Row_number() is better in this case ?

ste5an

Cause you'll get a different, simpler execution plan. Which, indeed, often means faster execution (but not necessarily). Without testing it and taking your own measures, it's only theoretical.

So install AdventureWorks and try to apply this kind of queries to an appropriate query. Use SET STATISTICS IO on and compare execution plans to see what happens.

marrowyung

ASKER

"So install AdventureWorks and try to apply this kind of queries to an appropriate query. Use SET STATISTICS IO on and compare execution plans to see what happens. "

but from SSMS, include client statistics is ok instead of set statistics IO, right?

just can't see why Row_number() can make the plan simplier, that's what I mean

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial