Will SQL Server be smart enough to not execute expensive queries if it is not needed ? (short-circuiting)

Question

So SQL Server does not have short-circuiting in the explicit manner as with for example if-statements in general-purpose programming languages.

So consider the following mock-up query:

SELECT * FROM someTable
WHERE name = 'someValue' OR name in (*some extremely expensive nested sub-query only needed to cover 0.01% of cases*)

Let's say there are only 3 rows in the table and all of them match name = 'someValue'. Will the expensive sub-query ever run? Let's say there are 3 million rows and all but 1 could be fetched with the name = 'someValue' except 1 row which need to be fetched with the sub-query. Will the sub-query ever be evaluated when it is not needed?

If one has a similar real case, one might be ok with letting the 0.01% wait for the expensive sub-query to run before getting the results as long as the results are fetched quickly without the sub-query for the 99.99% of cases. (I know that my specific example above could be handled explicitly with IF-statements in an SP, as suggested in this related thread: Sql short circuit OR or conditional exists in where clause but let's assume that is not an option.)

Answer 1

As the comments point out, the optimizer in SQL Server is pretty smart.

You could attempt the short-circuiting by using case. As the documentation states:

The CASE expression evaluates its conditions sequentially and stops with the first condition whose condition is satisfied.

Note that there are some exceptions involving aggregation. So, you could do:

SELECT t.*
FROM someTable t
WHERE 'true' = (CASE WHEN t.name = 'someValue' THEN 'true'
                     WHEN t.name in (*some extremely expensive nested sub-query only needed to cover 0.01% of cases*)
                     THEN 'true'
                END)

This type of enforced ordering is generally considered a bad idea. One exception is when one of the paths might involve an error,such as a type conversion error) -- however, that is generally fixed nowadays with the try_ functions.

In your case, I suspect that replacing the IN with EXISTS and using appropriate indexes might eliminate almost all the performance penalty of the subquery. However, that is a different matter.