CTE in SQL 2005

Common Table Expressions in SQL 2005 rule! One of the best uses of CTE is to do recursive queries. The syntax for it is as follows:

WITH myCTE (id, name, desc) AS

(

select id, name, desc

from table

you can reference the CTE here

)

select * from myCTE

The concept is similar to using derived tables in the way that your query can reference the CTE or derived table. The difference is that in a derived table you can only reference it after the derived table query has run. In a CTE, you can reference it while executing your query. And this makes it easy to build recursive queries.