title: SQL - Why Use CTEs?
body: <p>So, I'm an intermediate level SQL developer and recently I've been seeing a ton of discussions surrounding CTEs (Common Table Expressions). Everywhere I look, I see people saying that it's this amazing thing:</p>
  
  <blockquote>
    <p>CTEs (Common Table Expressions) are one of the most beautiful and the most powerful feature of SQL Server and are boon to SQL Server developers. These not only simplifies most of the complex operations in T-SQL but also do a lot more than one can imagine.</p>
    
    <p>User Comment from the Microsoft Technet article on using CTEs.</p>
  </blockquote>
  
  <p>From what I've seen myself and read online, a CTE is essentially just a "self-populating" temp table that can only be used once.  The only benefit I see is that the CTE cleans itself up, unlike the temp table which is bound to the session.  I've also seen a lot of comments about recursion but have never come across a case where I would need to use it; I feel like this is very specific to a certain type of problem?</p>
  
  <p><strong>In short:</strong> Are CTEs severely circumstantial or am I missing something hugely powerful here?  I've yet to see any real benefit to using a CTE over a temp table.</p>