SQL Common Table Expressions (CTEs) 3 — Questions and Answers
Question 1: What keyword is required to define a recursive CTE in standard SQL?
- RECURSIVE (Correct answer)
- LOOP
- REPEAT
- CYCLE
Correct answer: RECURSIVE
Standard SQL requires WITH RECURSIVE to define a recursive CTE.
Question 2: A recursive CTE consists of which two parts joined together?
- An anchor member and a recursive member (Correct answer)
- A header and a footer
- A SELECT and a GROUP BY
- An index and a key
Correct answer: An anchor member and a recursive member
Recursive CTEs combine an anchor (base) member with a recursive member, usually via UNION ALL.
Question 3: Which set operator typically connects the anchor and recursive members of a recursive CTE?
- UNION ALL (Correct answer)
- INNER JOIN
- INTERSECT
- EXCEPT
Correct answer: UNION ALL
UNION ALL is standard for combining the anchor and recursive members of a recursive CTE.
Question 4: Recursive CTEs are especially well suited to querying which kind of data?
- Hierarchical or tree-structured data like org charts (Correct answer)
- Flat lookup tables
- Binary blob data
- Encrypted columns
Correct answer: Hierarchical or tree-structured data like org charts
Recursive CTEs excel at traversing hierarchies such as organizational charts or bill-of-materials.
Question 5: What stops a recursive CTE from running indefinitely?
- The recursive member eventually returns no rows (Correct answer)
- A mandatory LIMIT clause
- The database timeout only
- A COMMIT statement
Correct answer: The recursive member eventually returns no rows
Recursion terminates naturally when the recursive member produces no additional rows.
Question 6: In SQL Server, what is the default maximum recursion level before an error is raised?
- 100 (Correct answer)
- 10
- 1000
- Unlimited
Correct answer: 100
SQL Server defaults to a maximum recursion of 100, adjustable with the MAXRECURSION option.
Question 7: Why might a recursive CTE generating a sequence of numbers need a termination condition in the recursive member?
- To prevent infinite recursion by bounding the values (Correct answer)
- To improve formatting
- To enable indexing
- To allow sorting
Correct answer: To prevent infinite recursion by bounding the values
A WHERE condition in the recursive member bounds growth and prevents infinite recursion.
SQL Common Table Expressions (CTEs) 3
What keyword is required to define a recursive CTE in standard SQL?