CTEs and recursive CTEs

A common table expression names a query for the duration of one statement:

WITH big_orders AS (
  SELECT customer_id, total FROM orders WHERE total > 1000
)
SELECT customer_id, COUNT(*) FROM big_orders GROUP BY customer_id;

Two T-SQL details matter. The statement before WITH must end with a semicolon, because WITH also introduces table hints and other clauses; that is why you see ;WITH in old code. And a non-recursive CTE is expanded into the query that uses it, like a view: SQL Server does not store its result. If you reference it twice, it is part of the plan twice. When you need a result computed once and reused, put it in a temporary table.

Recursion: anchor, recursive member, UNION ALL

A recursive CTE has two parts joined by UNION ALL. The anchor runs once and produces the starting rows. The recursive member refers to the CTE itself and runs again and again, each time on the rows the previous step produced, until a step produces nothing.

WITH walk AS (
  SELECT ...                       -- anchor: the starting rows
  FROM some_table WHERE <where to start>
  UNION ALL
  SELECT ...                       -- recursive member: one step further
  FROM some_table AS t
  JOIN walk AS w ON <t is one step away from w>
  WHERE <keep going?>
)
SELECT * FROM walk;

Carry what you need along the way: a step counter, or a trail string built by concatenation. String columns in a recursive CTE must have the same type in both members, which is why the example casts its trail to NVARCHAR(200) in the anchor.

The walk can go up a hierarchy as well as down, and it can stop early. Below, an expense climbs the management chain from the person who filed it until it reaches a manager whose approval limit covers the amount; the WHERE in the recursive member is what stops it:

1CREATE TABLE staff (id INT PRIMARY KEY, name NVARCHAR(20) NOT NULL, manager_id INT NULL, approval_limit INT NOT NULL);2INSERT INTO staff VALUES3  (1, N'Tamar', NULL, 50000), (2, N'Nino', 1, 5000), (3, N'Luka', 1, 8000),4  (4, N'Ana', 2, 1000), (5, N'Giorgi', 4, 0), (6, N'Eka', 3, 500);5CREATE TABLE expenses (id INT PRIMARY KEY, filed_by INT NOT NULL, amount INT NOT NULL);6INSERT INTO expenses VALUES (101, 5, 3000), (102, 6, 90000);

1WITH route AS (2  SELECT x.id AS expense, x.amount, 1 AS step, m.id, m.name, m.manager_id, m.approval_limit,3         CAST(m.name AS NVARCHAR(200)) AS trail4  FROM expenses AS x5  JOIN staff AS filer ON filer.id = x.filed_by6  JOIN staff AS m ON m.id = filer.manager_id7  UNION ALL8  SELECT r.expense, r.amount, r.step + 1, m.id, m.name, m.manager_id, m.approval_limit,9         CAST(r.trail + N' -> ' + m.name AS NVARCHAR(200))10  FROM route AS r11  JOIN staff AS m ON m.id = r.manager_id12  WHERE r.approval_limit < r.amount13)14SELECT expense, step, name, approval_limit,15       CASE WHEN approval_limit >= amount THEN N'approves' ELSE N'passes up' END AS decision,16       trail17FROM route18ORDER BY expense, step;

MAXRECURSION

To protect against an infinite loop, SQL Server stops a recursive CTE after 100 recursion levels by default and fails the statement with error 530. Raise or lift the limit with a query hint on the outer statement: OPTION (MAXRECURSION 1000), or 0 for no limit. With 0, a cycle in the data (A manages B, B manages A) loops until something else stops it, so prefer a real upper bound.

1DECLARE @log TABLE (attempt NVARCHAR(30), outcome NVARCHAR(40));2DECLARE @n INT;3 4BEGIN TRY5  WITH numbers AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 200)6  SELECT @n = COUNT(*) FROM numbers;7  INSERT INTO @log VALUES (N'default limit', CONCAT(@n, N' rows'));8END TRY9BEGIN CATCH10  INSERT INTO @log VALUES (N'default limit', CONCAT(N'error ', ERROR_NUMBER()));11END CATCH;12 13WITH numbers AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 200)14SELECT @n = COUNT(*) FROM numbers15OPTION (MAXRECURSION 500);16INSERT INTO @log VALUES (N'MAXRECURSION 500', CONCAT(@n, N' rows'));17 18SELECT attempt, outcome FROM @log;

Common mistakes

• UNION instead of UNION ALL. A recursive CTE requires UNION ALL between anchor and recursive member.
• Forgetting the limit of 100. A hierarchy that is 101 levels deep fails in production long after the tests passed; the second example reports error 530.
• Using a CTE as a cache. It is re-evaluated wherever it is referenced; it is not a temp table.
• Mismatched types in a growing string column such as a path or a trail. Without a cast, the anchor's type and the recursive member's type differ and the CTE fails.

What interviewers ask

• Is a CTE materialised in SQL Server? No; it is expanded inline like a view. Use a temp table to compute something once.
• How does a recursive CTE terminate? When the recursive member returns no rows, or with error 530 when it hits MAXRECURSION (100 by default).
• How would you list everyone under a manager with their level? Anchor on the manager's direct reports at depth 1, join employees to the CTE on manager_id, add 1 per step.