PrismitaLEARN · PRACTICE · GROW

What is a recursive CTE, and what is it used for?

SQL Server T-SQL Advanced 3–5 Yrs5+ Yrs SQL Query 1 min read

Quick Answer

A recursive CTE references itself to walk hierarchical or graph data — an org chart, a category tree, a bill of materials, a chain of referrals. It has an anchor member (the starting rows) UNION ALL a recursive member that joins back to the CTE, iterating until no new rows are produced.

A recursive CTE calls itself to traverse tree or graph structures. You write an anchor query for the starting point — say the CEO, or a top-level category — then UNION ALL a recursive part that joins the employees table back to the CTE to get the next level down, and SQL Server keeps applying that until it stops finding new rows. I always carry a level counter and set MAXRECURSION so a bad data cycle doesn't run forever. If the hierarchy is large and queried constantly, a closure table or hierarchyid performs better.

  • Guard against infinite loops with OPTION (MAXRECURSION 100) (default cap is 100; 0 means unlimited — dangerous).
  • Carry a Level column and/or a materialised path for ordering and cycle detection.
  • For deep or hot hierarchy queries, consider the hierarchyid type or a closure table instead.
All SQL Server questions More on T-SQL