• I got bitten by the same problem a while ago, see MS Connect

    It's nothing to do with cached temporary tables, just a "feature" of the way the query optimizer works.

    The MS response was "in general letting the optimizer choose any predicate evaluation order is necessary to give us the flexibility to choose the best query plan".

    Even one that fails?

    P.S. The behaviour is still there in SQL Server 2014.