I was also looking for the trick:)

Regarding the aggregates & case evaluation:

the CASE evaluation order is guaranteed for the SELECT phase. I think it helps to understand it when one thinks about the aggregation as a type of "subquery".

Example: The following query throws an error because the aggregate "MIN(1/0)" cannot be calculated.

DECLARE @i int = 1;

SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END Agg

FROM sys.objects

This query is logically the same as the following query:

DECLARE @i int = 1

SELECT CASE WHEN @i = 1 THEN 1 ELSE AggSub END Agg

FROM (SELECT MIN(1/0) AggSub

FROM sys.objects

) S

I think that in this version, one can easier see when the error actually happens --> before the case evaluation starts. In the first version, the location of the aggregate function can mislead people to assume that this aggregate is only calculated during the evaluation of the CASE statement. But it is not. It is already performed before the CASE evaluation starts.

And to provide an example with aggregates that shows that the evaluation order is honored, consider this one:

DECLARE @i int = 1

SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1) / 0 END Agg

FROM sys.objects

In this example, there is no error in the aggregation phase, as MIN(1) produces no error.

And there is no error in the SELECT phase, because the CASE evaluation stops right after the first WHEN (which is before the ELSE part which would cause an error).

And for completeness, here is the version in Subquery format:

DECLARE @i int = 1

SELECT CASE WHEN @i = 1 THEN 1 ELSE AggSub / 0 END Agg

FROM (SELECT MIN(1) AggSub

FROM sys.objects

) S