Microsoft® SQL Server 2008 R2 Unleashed

Recursive Queries with CTEs

Nonrecursive CTEs are ANSI SQL-99 compliant expressions that provide T-SQL coding flexibility. However, for each nonrecursive CTE, there is usually another T-SQL construct that can be used to achieve the same results (for example, derived tables). The real power and capability of CTEs is revealed when you use them to create recursive queries.

A recursive CTE can help simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. Recursive queries are often useful for expanding a hierarchy stored in a relational table (for example, displaying employees in an organizational chart). In previous versions of SQL Server, a recursive query usually required using temporary ...