TheSQLGuru (6/2/2009)Maybe with that many rows the optimizer is simply hoping it "gets lucky" and the seek/lookup plan is less costly that the massive cost of a table scan! :w00t:
Maybe 🙂
I am still confused by this - because I am getting optimal plans for each case with optional criteria and I should not. At least, according to everything I have read on this thread and others - using this format I should not be getting an optimal plan.
I have also tested this by removing the check for an is null parameter - the plan is the same, uses the same indexes and performs the same for the different possibilities.
I am really wondering if setting the parameterization to forced is why this works. I really need to setup a test database and check this out.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs