CREATE VIEW dbo.vw_BatchItemsSELECT BatchID, FieldA, FieldB, CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY DateEntered), 0) AS INT) AS CheckOrderFROM <myDatabase>.<mySchema>.<myTable>;
CREATE VIEW dbo.usp_sel_BatchItems (@BatchID INT)ASSELECT *FROM <myDatabase>.<mySchema>.vw_BatchItemsWHERE BatchID = @BatchID;
SQL Server 2008R2 build 4266USE AdventureWorksgoCREATE VIEW test1 asSELECT ProductID, CarrierTrackingNumber, UnitPrice, CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ModifiedDate), 0) AS INT) AS rownumFROM Sales.SalesOrderDetail2 records, INDEX seek, KEY lookup AS expectedSELECT *FROM test1WHERE ProductID = 897 --7073083 records, table scan AS expectedSELECT *FROM test1WHERE ProductID = 707Why are you doing the CAST ISNULL thing? Seems unnecessary to me, and this view avoids that "unnecessary" ComputeScalar in the plan:CREATE VIEW test2 asSELECT ProductID, CarrierTrackingNumber, UnitPrice, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ModifiedDate) AS rownumFROM Sales.SalesOrderDetail
SELECT *FROM <myDatabase>.<mySchema>.vw_BatchItemsWHERE BatchID = @BatchIDOPTION ( RECOMPILE );