hi curious_sqldba,

Its hard to describe your situation because query optimizer chooses a good enough plan depending on circumstance. For example, the number of records returned will have influence whether the query plan should utilize an index or might choose to do a table scan instead.

If the view is just a standard view containing just the tsql, then it would work very similarly to executing the query directly, except you can't have an ORDER BY in the view unless TOP, OFFSET or FOR XML is also specified.

I can only describe a hypothetical query below

select a.SalesOrderID, a.CustomerID, b.CarrierTrackingNumber

from [Sales].[SalesOrderHeader] a join [Sales].[SalesOrderDetail] b

on a.SalesOrderID = b.SalesOrderID

where a.SalesOrderID = 43659

order by a.SalesOrderID, a.CustomerID, b.CarrierTrackingNumber

1 record returned from SalesOrderHeader and 12 records returned from SalesOrderDetail. In this case, it would be efficient to perform an index seek on SalesOrderHeader.PK_SalesOrderHeader_SalesOrderID, get SalesOrderID and CustomerID and use this table as a "base". In another word, the "base" table will provide input to the other join table SalesOrderDetail using a physical operation Nested Loops. SalesOrderDetail will use the provided SalesOrderID as a join condition to perform an index seeks on SalesOrderDetail.PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID to get the 12 CarrierTrackingNumber. CarrierTrackingNumber needs to be sorted before the result is combined to the main resultset. Sort is not required for SalesOrderHeader because there's only 1 record returned.

This is an over-simplified explanation of such operation. I don't think I can cover in more detail in a discussion thread. Hope it helps.