← Back to Knowledge Base← Previous questionNext question →
ProgrammingSQL Developer / Backend Developer

Describe the various ways to implement complex iterative calculations in SQL. When should recursive Common Table Expressions (CTEs) be used, and when is it better to resort to other techniques?

Pass interviews with Hintsage AI assistant

Answer.

There are several ways to implement iterative/recursive calculations in SQL:

  • Recursive CTE (WITH RECURSIVE) — work for tasks like traversing hierarchies (trees, graphs, chains of links).
  • Cursors (CURSOR) — allow processing data row by row, but are less efficient for large volumes than set-based operations.
  • Regular set-based expressions — preferred for most cases (operate on sets of rows without iterations).

When to use recursive CTE:

  • For hierarchical structures (product categories, organizational structures).
  • For finding paths/lengths of chains (e.g., genealogy, dependency graphs).

Example:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS level
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.level + 1
  FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

If a recursive CTE does not solve the problem or the data is too large, other mechanisms are applied (such as external processing, temporary tables, or specialized algorithms outside SQL).

Trick question.

Can a recursive CTE lead to infinite execution? How can this be prevented?

Answer: Yes, if there are cycles in the graph (for example, parent_id refers back to a descendant), the recursive CTE can loop indefinitely. Most DBMSs support limiting the maximum number of recursion levels (MAXRECURSION for MS SQL or a similar option). A good practice is to always limit recursion depth.

-- MS SQL Server
OPTION (MAXRECURSION 100)

Examples of real mistakes due to ignorance of the nuances of the topic.

Story

In a financial project, the final balance was calculated through chains of linked accounts (parent-child) using a recursive CTE. One user created a cycle in the structure (a parent became its own descendant). The query began executing indefinitely, burdening the server. Cycle checks and MAXRECURSION were added, and the problem was resolved.

Story

In a task management system, the status of dependent tasks was computed through cursor processing. After migrating to a recursive CTE, optimal indexes on parent_id were forgotten, resulting in a 5-fold decrease in performance as costly join operations occurred at each step.

Story

A query to traverse a category tree using a cursor worked correctly but took 7 minutes for 100,000 rows. It was rewritten to use a recursive CTE, which was considered "slow" — achieving the same result in 5 seconds due to optimization by the set processing engine.