How to avoid Table Spool and why there is a JOIN without predicate?

Darth Sonic 2014-12-16 08:49:36

The Table Spool is the expensivest part of query. How to avoid it calling a view with no row returning in this case?

And why is there a join witout predicate when joinging the view even if the view itself has joins with proper predicates and the outer apply does have where clause? If not it would return thousands of rows and not non. Even doing a left join would not fix this (plan remains exactly the same).