From this vantage point its very hard to give you a definitive answer, as the definition of the view,relationships, indexes and quantity of rows are all unknown.

This sort of issue...

Interesting.....

I can now get multiple index seeks 😀

drop proc test2

go

create proc test2 @ProductId integer,

@ReferenceOrderID...

Jeffrey ,

Can you post a full XML query plan of one of your queries that Seek ?.

If you could try to make the query as simple as possible though,...

Wouldn't the scan be expected with the additional criteria since Quantity is not part of nor included in the index?

If I make the index ProductID, Quantity or add Quantity as...

Ive Accumlated all the methods i can think of into one proc,

drop proc test2

go

create proc test2 (@productid int = null, @quantity int = null)

as

set nocount on

select TransactionId

from [Production].[TransactionHistory]

where (@productid is...

I dont think you attached the right plan, that one has an index scan

FWIW , i also get an index scan , i guess this conversation just goes to prove the sensitivity of this sort of query.

Microsoft SQL Server 2005 - 9.00.4207.00...

http://www.sommarskog.se/dynamic_sql.html This one is also very good discussing dynamic sql in general. Coincidently I posted to my blog a method for optimizing dynamic sql for Unbalanced data loads...

http://www.sommarskog.se/dyn-search-2005.html discusses this and many other similar methods