GilaMonster (6/2/2009)

---

TheSQLGuru (6/2/2009)

---

Interesting. You also get a seek if you simply hard code values for coalesce (and I presume isnull) such as this example. It avoids having to hit the table for the pair of MAXs:

Yup, though as soon as you add a second condition, it goes back to a clustered index scan (at least for me). What do you get here? Index scan/Bookmark Lookup?

ALTER PROCEDURE Test2 ( @ProdID int = null, @Qty int = null)

AS

select TransactionID

from [Production].[TransactionHistory]

where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits of INT here!

AND Quantity BETWEEN coalesce(@Qty, 0) AND coalesce(@Qty, 99999999)

GO

Exec Test2 @prodID = 790