• Interesting. Looks like you hit a similar problem to this:

    http://www.glorf.it/blog/2008/05/16/sql-talk/sql-server-is-not-aware-of-nondeterministic-functions

    In query #1, the non-deterministic function in the subquery is applied after the join (also note the "No Join Predicate" warning in the join operator.

    The non-deterministic subquery has already been materialised in query #2, therefore is guaranteed to be correct.