Interesting.....

I can now get multiple index seeks 😀

drop proc test2

go

create proc test2 @ProductId integer,

@ReferenceOrderID integer,

@Quantity integer

as

select *

from [Production].[TransactionHistory]

where (@productid is not null and @productid = ProductID)

or (@ReferenceOrderID is not null and @ReferenceOrderID = ReferenceOrderID)

--or (@quantity is not null and @quantity = Quantity) Uncomment This to force a scan

go

exec test2 @productid = 790,@ReferenceOrderID = NULL,@Quantity=NULL

go

exec test2 @productid = NULL,@ReferenceOrderID =61197 ,@Quantity=NULL

It seems to seek if ALL the referenced columns are indexed, but uncommenting the Quantity lookup a scan takes place.

Edit : Ive updated my blog http://sqlandthelike.blogspot.com/ with this info