tafountain (3/21/2013)
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.
I'm not sure it's totally true.
At the moment - not sure.
As I said, in my test on Tally table I was getting Index Seek + Key Lookup when the number of records selected by WHERE clause was 120 or below.
Execution plan was changed to Table Scan followed by Filter (WHERE) when the number was approaching 150.
So, it's not necessarily to get a table scan when you're doing ROW_COUNT over partition. It only happens when optimizer decides that Key Lookup would be too expensive for the recordset.
But anyway - I salute you decision to go with pre-calculated and stored ranking.
It's still waaaaaay more effective than any of the solutions within SELECT queries, as now the ranking is ccalculated once (may be several times if there are updates to stored data) rather than every time users retrieve it.
_____________
Code for TallyGenerator