How to rewrite CASE expression to short-circuit evaluation
What is currently happening

When running your query, the table scan, stream agg &amp; compute scalar operators are not evaluated at runtime.







Why is it happening

The apply NL join means that for each row in #Docs, return a row from #Docsitems that matches the predicate. This predicate should be WHERE IDDocs = D.ID

But the compute scalar operator (EXPR1007) next to the select (the actual case statement) is calling the scalar function in the inner side of the NL apply (EXPR1005) only when Doctype &lt;&gt; 1. This as you know cannot happen, and they both return NULL.

Compute scalar between NL &amp; SELECT:



Compute scalar on the inner side of the join:



This all seems to be due to how the CASE statement functions &amp; removing literals. (difference between CASE  2 = 1 vs. CASE WHEN D.DocType &lt;&gt; 1)



Resolving

If you change the query to this:

SELECT
    -- expression
    SumAmount = 
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType &lt;&gt; 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False


You should get the execution plan you desire:





Removing the SELonLOJ rule that is changing the plan on the rewrite. 

Guessing

Reverting the rule that was applied to get closer to the CASE WHEN query can be done by adding the hint: OPTION( QUERYRULEOFF SELonLOJ   )

SELECT
    -- expression
    SumAmount = 
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType &lt;&gt; 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF SELonLOJ   );




Showing the same situation (minus filter &amp; left join) as the case statement, but the runtime elimination is not occuring.



&amp;





Another rule that seems to get closer to the case when when turning it off is JoinPredNorm   

SELECT
    -- expression
    SumAmount = 
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType &lt;&gt; 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF JoinPredNorm   )


With the filtering happening on the #Docs table as expected.