• It is difficult to demonstrate for me now because I am not at the office, the computer I use now does not have SQL... sorry if I take time to get there 🙂

    Though this is what the final EP looks like, with such a query. Note that the syntax is way different from the CTE, but the principle is the same.

    Select *

    From [User] u

    Where

    (

    Id In (Select Id From [User] Where @FirstName = FirstName And @FirstName Is Not Null)

    Or Id In (Select Id From [User] Where @LastName = LastName And @LastName Is Not Null)

    Or Id In (Select Id From [User] Where @Email = Email And @Email Is Not Null)

    Or Id = @Id

    )

    Actually in this very specific example, the conditions "And @FirstName Is Not Null" are optional because nothing can be equal to Null. But I encourage developers to write it down anyway so the dynamic triggering condition is explicit and though more readable.

    This is the execution plan:

    Actually the execution plan will differ from the implementations (merge join, loop joins, etc). The principle is to have no big scan remaining which is not on purpose.

    At least, if we still can discuss about the ways to get there :), it shows the interest of having everything in one single eyeshot. It shows all the possible paths of execution SQL can take, keeping in mind that because of scalar resolution most of the time only one path will be executed, while there will be only one EP in the cache plan to handle all possibilities.

    This is helpful when driving a project, because it is more simple for the developer, the DBA, and the team leader to review the query entirely, look at what has been optimized with indexes (maybe we don't want to optimize everything for disk space reasons), and pinpoint the nasty corner cases ("ugh! did you see you have an index scan when no parameter is given? Is this what you want?"). The predictability is immediate, while I found that using dynamic concatenated SQL statements forces to review each one of them, and the number of possible combinations makes that one day, some use case is missed and performs badly on production.