tafountain (3/21/2013)
@Matt & Sergiy - I'll have to repost a better example. I think the point was missed. The predicate applied by the WHERE clause is not pushed down to the view at runtime. This is evidenced by reviewing the execution plan and seeing that a "clustered index scan" is being performed on the underlying table *and* returning all the rows in the table. A few operations later the predicate is applied. My whole point is the ranking function should only execute over the returned data (i.e. the predicate being applied first), not all of the data in the table. This is further illustrated by 2 things - first I removed the ranking function from the view and second, putting the ranking function in the actual select statement over the view. Both examples show the appropriate index seek and only one record is returned.I'll post better and more elaborate examples later.
I'm not sure why you say "the ranking function should only execute over the returned data (i.e., the predicate being applied first), not all of the data in the table." The optimizer can rearrange queries into any logically equivalent form using a number of rules. Predicate pushdown is one way the optimizer can simplify a query, but the optimizer may still estimate the cost of a plan that doesn't push the predicate down to be less than one that does. As I mentioned above, the optimizer likely would estimate the cost of scanning a 60,000-row table to be very low - low enough, in fact, that it might decide that it would cost more to generate and evaluate additional execution plans than to just execute the query with the scan.
Could you post actual execution plans for both queries - with and without the ROW_NUMBER() function call?
Jason Wolfkill