title: SQL Server 2008: Join VIEW with other VIEW: precalculate without resorting to temp tables
body: <p>To perform transformations in my database, I frequently use a chained set of views. Within the views will be common table expressions. For example I would have the following:</p>
  
  <pre><code>CREATE VIEW TransformationStep1 AS
      WITH Transformation1A AS (
          SELECT Field1, Field2, Field3, Bla(Field1) AS Calc FROM Table1
      ),
      Transformation1B AS (
          SELECT Field1, Field2, Field3, Calc FROM Transformation1A
      )
      SELECT * FROM Transformation1B
  
  CREATE VIEW TransformationStep2 AS
      WITH Transformation2A AS (
          SELECT Field1, Calc FROM TransformationStep1
      ), ....
  </code></pre>
  
  <p>Somewhere near TransformationStep4 the views will become slower as the query plan becomes more complicated. This is expected and OK. </p>
  
  <p>But when I want to join a part of TransformationStep4 to itself, the query will slow down hugely as the optimizer tries to find its way back all the way to the source tables and look for indexes of some kind. Normally this is OK, but sometimes I just want to store my temporary result and join with that because (being the designing human in the story) I for one know that the result table will be quite small, and it will be much faster to join against a "prefetch" of it.</p>
  
  <p>Is there a way for me to write a query hint, that will influence the query plan in such a way that the subquery will be prefetched and then joined against? Otherwise I'll have to resort to temp tables in a stored procedure, but I want to avoid it if I can.</p>
  
  <p>Thanks for any suggestions, also when you think my design sucks :-)</p>