Introduction to recursive CTE

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

The recursive CTE

Consists of 4 parts:

WITH cte_name AS (

    -- Anchor member
   initial_query

   UNION ALL
   -- Recursive member
   recursive_query
   termination_condition
 )

SELECT *
    FROM cte_name

Guide to using a recursive CTE

For more than 100 recursion steps, increase the number of recursion steps:
- OPTION(MAXRECURSION 32767)
The following SQL statements are not allowed: GROUP BY, HAVING, LEFT JOIN, RIGHT JOIN, OUTER JOIN, SELECT DISTINCT, Subqueries,TOP
The number of columns for anchor and recursive member are the same
The data types of anchor and recursive member are the same

Recursive example

Calculating the factorial:

The factorial of n is defined by the product of all positive integers less than or equal to n:

3! = 1 x 2 x 3 = 6

The factorial n! is defined recursively as follows:

0! = 1 for iteration = 1
(n+1)! = n! * (iteration+1) for iteration > 1

Recursive example in code

WITH recursion AS

     (SELECT 1 AS iterationCounter,1 AS factorial

     UNION ALL
     SELECT iterationCounter+1,factorial * (iterationCounter+1)
         FROM recursion

         WHERE iterationCounter < 10 )

SELECT factorial 
    FROM recursion

Let's practice!

Hierarchical and Recursive Queries in SQL Server