Common Table Expressions (CTEs) Flashcards
7 cards from real SQL practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 Common Table Expressions (CTEs) flashcards as text
What keyword is required to define a recursive CTE in standard SQL?
Answer: RECURSIVE
Standard SQL requires WITH RECURSIVE to define a recursive CTE.
A recursive CTE consists of which two parts joined together?
Answer: An anchor member and a recursive member
Recursive CTEs combine an anchor (base) member with a recursive member, usually via UNION ALL.
Which set operator typically connects the anchor and recursive members of a recursive CTE?
Answer: UNION ALL
UNION ALL is standard for combining the anchor and recursive members of a recursive CTE.
Recursive CTEs are especially well suited to querying which kind of data?
Answer: Hierarchical or tree-structured data like org charts
Recursive CTEs excel at traversing hierarchies such as organizational charts or bill-of-materials.
What stops a recursive CTE from running indefinitely?
Answer: The recursive member eventually returns no rows
Recursion terminates naturally when the recursive member produces no additional rows.
In SQL Server, what is the default maximum recursion level before an error is raised?
Answer: 100
SQL Server defaults to a maximum recursion of 100, adjustable with the MAXRECURSION option.
Why might a recursive CTE generating a sequence of numbers need a termination condition in the recursive member?
Answer: To prevent infinite recursion by bounding the values
A WHERE condition in the recursive member bounds growth and prevents infinite recursion.