title: How CTE works internal in sql?
body: <p>I'm having a problem to track the Sequence of executing the following code : </p>
  
  <p><strong>The Code is working fine</strong></p>
  
  <p>Im just trying to understand how.</p>
  
  <pre><code>    with MyCTE(x)
      as
      (
    1)  select x = convert(varchar(8000),'hello') // line 1
      union all
    3)  select x + 'a' from MyCTE where len(x) &lt; 100 //line 3
      )
      select x from MyCTE
      order by x
  </code></pre>
  
  <p><strong>MSDN:</strong></p>
  
  <blockquote>
    <p>The semantics of the recursive execution is as follows:</p>
    
    <p>Split the CTE expression into anchor and recursive members.</p>
    
    <p>Run the anchor member(s) creating the first invocation or base result
    set (T0).</p>
    
    <p>Run the recursive member(s) with Ti as an input and Ti+1 as an output.</p>
    
    <p>Repeat step 3 until an empty set is returned.</p>
    
    <p>Return the result set. This is a UNION ALL of T0 to Tn.</p>
  </blockquote>
  
  <p><strong>Stages:</strong></p>
  
  <p>1) line 1 is executed  ( x=hello)</p>
  
  <p>2) line 3 is executed (helloa)</p>
  
  <p>3) now it calls itself so : here x is again back to hello !! ( line 1)</p>
  
  <ul>
  <li><p>according to : line <strong>1</strong> ,  whenever the cte calls itself - the x is always should be  reset ! ( or does the T0 is bypassed in the recursive?)</p></li>
  <li><p>what is the role for the (x) part it MyCTE(x) ? input or output ?</p></li>
  </ul>
  
  <p><strong>quote:</strong></p>
  
  <blockquote>
    <p>Run the recursive member(s) with Ti as an input and Ti+1 as an output.</p>
  </blockquote>
  
  <p>To my knowledge the (x) is the out value, not the Input.</p>