SQL Server CTE Recursion Limit
SQL Server CTE Recursion Limit is a SQL statement in the SQL Server Specific category. SQL Server: limits recursive CTE depth (default 100). MAXRECURSION 0 = unlimited (risk of infinite loop). The syntax is WITH cte AS (...) SELECT * FROM cte OPTION (MAXRECURSION 1000);. It returns recursion-limited result. A typical example: WITH RECURSIVE org_tree AS ( SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN org_tree t ON e.manager_id = t.id ) SELECT * FROM org_tree OPTION (MAXRECURSION 50); -- Limits to 50 levels deep -- Default max is 100; set to 0 for unlimited WITH RECURSIVE count_up AS ( SELECT 1 AS n UNION ALL SELECT n + 1… A close relative is OUTPUT Clause, which sQL Server: returns values from DML statements (like PostgreSQL RETURNING). A close relative is STUFF(), which sQL Server: deletes a specified length of characters from a string and inserts another string at the start position.