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:select TransactionID
from [Production].[TransactionHistory]
where ProductID BETWEEN coalesce(@ProductID, 0) AND coalesce(@ProductID, 99999999) --should use actual limits of INT here!
You don't get the seek/bookmark lookup if you do select * with that query. Still a CI scan.
I've used this in the past after having read an article about it somewhere, I don't think it was on SSC. Of course the Select * will give a scan because you'd have to do a seek and a lookup with the Select * so the scan is probably faster.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question