Force spool to avoid creating temp tables? I have the following query which join pivoted data of several very big tables and it runs very slow. I tried to created the temp table for each pivoting and it runs much faster. Is it a way to force index/table spooling without create temp tables? 

select ...
from T1 ....
left join (select ... from (select ... from T2....) pivot (sum(C1) on C2 in (...))) p1 
    on ...
left join (select ... from (select ... from T3....) pivot (sum(...) on ... in (...))) p2
    on ...
left join (select ... from (select ... from T4....) pivot (sum(...) on ... in (...))) p3
    on ...
A very similar question was asked and answered on StackOverflow: How can I force a subquery to perform as well as a #temp table?  To summarize, 


The most reliable method is simply to use a #temp table and materialize it yourself.
Failing that, see Provide a hint to force intermediate materialization of CTEs or derived tables. The use of TOP(large_number) ... ORDER BY can often encourage the result to be spooled rather than repeatedly re evaluated.  Even if that works however there are no statistics on the spool.
Another option is to use a user-defined function.  Multi-statement functions (as described in How to Share Data between Stored Procedures) appear to force the SQL Server to materialize the results of your subquery.  In addition, they allow you to specify primary keys and indexes on the resulting table to help the query optimizer.  This function can then be used in a select statement as part of your view.  For example:


CREATE FUNCTION SalesByStore (@storeid varchar(30))
   RETURNS @t TABLE (title varchar(80) NOT NULL PRIMARY KEY,
                     qty   smallint    NOT NULL)  AS
BEGIN
   INSERT @t (title, qty)
      SELECT t.title, s.qty
      FROM   sales s
      JOIN   titles t ON t.title_id = s.title_id
      WHERE  s.stor_id = @storeid
   RETURN
END

CREATE VIEW SalesData As
SELECT * FROM SalesByStore('6380')