456789psw (4/10/2013)

---

If anyone is curious like most things it depends..

If the CTE is using recursion then its uses a spool operator therefore tempdb is used....therefore not much performance benefit vs just creating a temp table

If the CTE is non recursive then then the main qry is repeated and performance is that of using a cursor..the main anchor query is repeated therefor performance is no good!

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/11/common-table-expressions-cte-s-how-it-works-how-recursion-works-using-with-adjacency-list.aspx%5B/quote%5D

??????