SQL Server Common Table Expressions (Recursive)

SQL Server Common Table Expressions (Recursive) is a SQL statement in the SQL Server Specific category. SQL Server CTEs support recursion with MAXRECURSION hint to limit depth (default 100). The syntax is WITH cte (...) AS (anchor UNION ALL recursive) SELECT ... OPTION (MAXRECURSION n);. It returns recursive CTE result. A typical example: WITH RECURSIVE number_series AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM number_series WHERE n < 1000 ) SELECT * FROM number_series OPTION (MAXRECURSION 1000); -- Generates numbers 1-1000 -- Without OPTION MAXRECURSION 0, default limit is 100 -- Calendar table generation: WITH RECURSIVE dates AS ( SELECT CAST('2026-01-01' AS DATE) AS dt UNION ALL SELECT DATEADD(DAY, 1, dt) FROM dates WHERE dt < '2026-12-31' )… 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.