SQL Server 2008R2 build 4266

USE AdventureWorks

go

CREATE VIEW test1 as

SELECT

ProductID,

CarrierTrackingNumber,

UnitPrice,

CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ModifiedDate), 0) AS INT) AS rownum

FROM Sales.SalesOrderDetail

2 records, INDEX seek, KEY lookup AS expected

SELECT *

FROM test1

WHERE ProductID = 897 --707

3083 records, table scan AS expected

SELECT *

FROM test1

WHERE ProductID = 707

Why are you doing the CAST ISNULL thing? Seems unnecessary to me, and this view avoids that "unnecessary" ComputeScalar in the plan:

CREATE VIEW test2 as

SELECT

ProductID,

CarrierTrackingNumber,

UnitPrice,

ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ModifiedDate) AS rownum

FROM Sales.SalesOrderDetail

Given the above, it is possible your build of SQL Server has a bug/regression in it (in which case you could try taking out the CAST and see if that makes a difference and also eliminate the view and see if you get a seek). MUCH more likely IMHO is that the number of rows you get out of the query simply make the optimizer pick a scan as the most efficient access mechanism. Try forcing the index seek and see if it is more efficient. I note this could also be a simple case of parameter sniffing, where the first batchid called has a high estimated rows and gets the scan? Maybe not given your comment about with and without the ROW_NUMBER. Again please try without the CAST. Also, if you put the ROW_NUMBER in the sproc, what effect do you get? Also try OPTION(RECOMPILE on the statement in the sproc to see effect...