MS SQL Server: CTEs for Readable Queries
Common Table Expressions break complex queries into understandable steps and enable recursive queries.
Key Insights
- CTEs make multi-step queries readable by naming each transformation
- Recursive CTEs handle tree traversal and hierarchical data natively
- CTEs are not materialized — they’re inlined by the optimizer like subqueries
Named Steps
WITH monthly_sales AS (
SELECT MONTH(order_date) AS month, SUM(total) AS revenue
FROM orders WHERE YEAR(order_date) = 2024
GROUP BY MONTH(order_date)
),
with_growth AS (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_sales
)
SELECT * FROM with_growth WHERE growth > 0;
Recursive CTE for Hierarchies
WITH org_chart AS (
SELECT id, name, manager_id, 0 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT REPLICATE(' ', level) + name AS org_tree FROM org_chart ORDER BY level;