title: SQL Server and intermediate materialization?
body: <p><em>After reading <a href="http://dataeducation.com/cursors-run-just-fine/" rel="nofollow noreferrer">this interesting article</a> about intermediate materialization - I still have some questions.</em> </p>
  
  <p>I have this query : </p>
  
  <pre><code>SELECT    *
  FROM  ...
  WHERE       isnumeric(MyCol)=1  and ( CAST( MyCol AS int)&gt;1) 
  </code></pre>
  
  <p>However, the where clause order is <strong>not</strong> deterministic.</p>
  
  <p>So I <em>might</em> get exception here.( if he first tries to cast <code>"k1k1"</code> )</p>
  
  <p>I assume this will solve the problem</p>
  
  <pre><code>SELECT MyCol 
  FROM
      (SELECT TOP 100 PERCENT foo From MyTable WHERE ISNUMERIC (MyCol ) &gt; 1 ORDER BY MyCol ) bar
  WHERE
      CAST(MyCol AS int) &gt; 100
  </code></pre>
  
  <ul>
  <li>why does putting <code>top 100 + order</code> will change VS my regular query ?</li>
  </ul>
  
  <p>I read in the comments  : </p>
  
  <blockquote>
    <p>(the "intermediate" result -- in other words, a result obtained during
    the process, that will be used to calculate the final result) will be
    physically stored ("materialized") in TempDB and used from there for
    the remainder of the user, instead of being queried back from the base
    tables.</p>
  </blockquote>
  
  <ul>
  <li>what difference does it makes if it is stored in tempDB or queried back from the base tables? it is the <strong>same data</strong> !</li>
  </ul>