• Lynn Pettis (5/7/2013)


    First, I don't think your question is really about why is the index on the view being selected as much as why the first query generates 5 index seeks while the second only one.

    That was what my other question was about 🙂

    Lynn Pettis (5/7/2013)


    The reason is the first generates 5 index seeks is that SQL Server has to generate a plan that covers all possible execution paths in the first query. The CASE statement is not evaluated during this phase, it is evaluated during the execution of the query itself.

    I am surprised that IF ELSE IF statements don't generate similar plans than CASE statements.



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort