Lynn Pettis (3/18/2013)
What happens if you take this out of the call to ROW_NUMBER(): PARTITION BY BatchID. Seem unnecessary since you are calling it with a specific BatchID, unless you also need the the view for other things with all the BatchD's present.You are probably getting the scan because of the PARTITION BY clause.
Lynn,
Unfortunately this isn't the case. Even with the PARTITION BY clause, SQL Server is still performing an index scan over the index and returning all the records in the table (as evidenced in the execution plan). I think this might be a bad usage of a ranking function (or possibly a bug in SQL Server for not pushing the predicate down into the view).