May include but is not limited to: recursive, non recursive
A common table expression is a powerful new tool which generates a named result set for the lifetime of the current query. In some cases, they can replace views (you don’t always need views), and therefore reducing complexity. They can be referenced by their name in the query multiple times, and they can be self-referencing, thus enabling recursion.
Here’s how a CTE looks like:
WITH Name of CTE
( List of columns)
( SELECT statement )
Outer SELECT statement
This structure is fairly useful, but let’s see the real cool feature, the self-referencing and the recursion:
WITH Numbers (n)
(SELECT 1 AS n
SELECT n + 1
WHERE n < 10)
SELECT n FROM Numbers
The sample above, which originates from Michael Coles: Pro T-SQL 2008 Programmer’s Guide p.255, does nothing but lists the numbers from one to ten. But it does this in a recursive manner, which opens up new horizons in data access code.
A last thing to remember: CTEs have a recursion level of a hundred, by default. You can override this behavior by specifying the MAXRECURSION option, can be 0 to 32767.