Recursive CTE for series generation in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Recursive CTE in SQL?

A Recursive CTE (Common Table Expression) is a temporary result set that references itself to perform repeated operations, often used to generate sequences or traverse hierarchical data.

beginner

How does a Recursive CTE generate a series of numbers?

It starts with an initial value (anchor member) and repeatedly adds rows by referencing itself (recursive member) until a stopping condition is met.

beginner

What are the two main parts of a Recursive CTE?

1. Anchor member: the starting query that runs once.<br>2. Recursive member: the query that references the CTE itself to add more rows.

beginner

Why do we need a stopping condition in a Recursive CTE?

To prevent infinite loops by telling SQL when to stop adding new rows in the recursion.

intermediate

Write a simple Recursive CTE to generate numbers from 1 to 5.

WITH Numbers AS ( SELECT 1 AS num UNION ALL SELECT num + 1 FROM Numbers WHERE num < 5 ) SELECT num FROM Numbers;

What does the anchor member in a Recursive CTE do?

A Starts the recursion with initial rows

B Stops the recursion

C References the CTE itself

D Deletes rows from the table

The anchor member provides the initial rows to start the recursion.

Which SQL keyword is used to combine the anchor and recursive members in a Recursive CTE?

A JOIN

B ORDER BY

C GROUP BY

D UNION ALL

UNION ALL combines the anchor and recursive queries to build the full result.

What happens if a Recursive CTE has no stopping condition?

A It returns only the anchor member rows

B It returns an empty result

C It runs infinitely or until max recursion limit

D It throws a syntax error

Without a stopping condition, recursion continues until SQL server stops it or runs out of resources.

Which of these is a valid use case for Recursive CTEs?

A Sorting a table alphabetically

B Generating a list of dates

C Updating multiple tables

D Creating indexes

Recursive CTEs are great for generating sequences like dates or numbers.

In the example: WITH Numbers AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM Numbers WHERE num < 5), what is the last number generated?

A 5

B 1

C 6

D 4

The recursion stops when num reaches 5, so 5 is the last number generated.

Explain how a Recursive CTE generates a series of numbers step-by-step.

Think about how the query calls itself repeatedly.

Describe why a stopping condition is important in Recursive CTEs and what might happen without it.

Consider what happens if the recursion never ends.