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

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.)

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.