Execution Plan ROW_NUMBER ()

Question

Please consider this request:

SELECT num, * FROM ( SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ROW_NUMBER() OVER(ORDER BY OrderID) AS num FROM Orders ) AS numbered WHERE NUM BETWEEN 0AND 100 

when I execute this request and get the execution plan, it looks like this:

I want to know

1) What steps do SQL Server 2008 pass to add ROW_NUMBER() to the query?

2) Why at the first stage in terms of implementation we have a Clustered Index Scan ?

3) Why is the cost of filtration 2%? I mean, why does the sql server not scan the table to get the corresponding data? Does ROW_NUMBER() index creation?

Answer 1

• Parts of the Segment / Sequence project of the plan relate to the use of ROW_NUMBER ().

• You have a clustered index scan because your inner SELECT does not have a WHERE clause, so all rows in the table should be returned.

• A filter refers to a WHERE clause on an external SELECT.

Answer 2

• This part of the "Calculate scalar" part of the query is created by row_number .
• Since you select each row from Orders , then number it, and then select 1-100. That the table (or in this case the clustered index) scans in any case, you cut it.
• No, indexes are not created on the fly. It should check the strings because the set does not return to your subquery.