title: Is CTE's stored (like a table) or do you create it each time you need it? (SQL Server 2005)
body: <p>Never worked with MS SQL Server before and the CTE's is new to me. But it's exactly what I need in the project I just started. </p>
  
  <p>Have a table that looks like</p>
  
  <pre><code>TABLE group (
   'id' int,
   'parentId' int,
   'groupName' varchar(255)
  )
  </code></pre>
  
  <p>So I have a dynamic page and depending on a get parameter, "Group" I want all that's below this group returned. I'm pretty sure I can manage to write the CTE for it as well as use it. </p>
  
  <p>But do I create it each time I wish to use it? (basically connect to DB, run the setup for the CTE, then use a query running on this). 
  Or are they stored in the database once they're created and ready to be used whenever?</p>
  
  <p>If they are stored, is there any command similar to sp_tables, etc to show it /them?
  Cause if they're saved there probably already exist a query that does this. </p>