Common Table Expressions (CTEs) are a powerful SQL Server feature that can dramatically improve query readability and maintainability.
Introduced in SQL Server 2005, CTEs let you define a temporary result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or MERGE statement.
Basic CTE Syntax
A CTE follows this pattern:
WITH CTE_Name AS (
-- Your query here
)
SELECT * FROM CTE_Name;
The main components are:
- The
WITH keyword to start the CTE
- A name for your CTE
- The
AS keyword
- Parentheses containing your query
- A statement that references the CTE
Why Use CTEs?
CTEs offer several advantages:
- Improved readability: Breaking complex queries into named, logical segments
- Self-referencing capability: Useful for hierarchical or recursive data
- Query simplification: Reducing nested subqueries
- Code reusability: Using the same temporary result multiple times in a query
Simple CTE Example
Here's a basic example that calculates average order values by customer category:
-- Without CTE
SELECT
c.CustomerCategory,
SUM(o.TotalAmount) / COUNT(DISTINCT o.OrderID) AS AvgOrderValue
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerCategory;
-- With CTE
WITH OrderSummary AS (
SELECT
c.CustomerCategory,
o.OrderID,
o.TotalAmount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
)
SELECT
CustomerCategory,
SUM(TotalAmount) / COUNT(DISTINCT OrderID) AS AvgOrderValue
FROM OrderSummary
GROUP BY CustomerCategory;
The CTE version clearly separates the data gathering from the aggregation logic.
Multiple CTEs in a Single Query
You can chain CTEs for even more complex scenarios:
WITH
CustomerOrders AS (
SELECT
c.CustomerID,
c.CustomerName,
COUNT(o.OrderID) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName
),
OrderCategories AS (
SELECT
CustomerID,
CASE
WHEN OrderCount = 0 THEN 'Inactive'
WHEN OrderCount BETWEEN 1 AND 5 THEN 'Regular'
ELSE 'VIP'
END AS CustomerCategory
FROM CustomerOrders
)
SELECT
c.CustomerName,
o.CustomerCategory
FROM CustomerOrders c
JOIN OrderCategories o ON c.CustomerID = o.CustomerID
ORDER BY o.CustomerCategory, c.CustomerName;
Recursive CTEs
One of the most powerful CTE features is recursion, which is perfect for hierarchical data like organizational charts or category trees:
WITH EmployeeHierarchy AS (
-- Anchor member (starting point)
SELECT
EmployeeID,
EmployeeName,
ManagerID,
0 AS Level
FROM Employees
WHERE ManagerID IS NULL -- Start with top-level employees
UNION ALL
-- Recursive member (references itself)
SELECT
e.EmployeeID,
e.EmployeeName,
e.ManagerID,
eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT
EmployeeID,
EmployeeName,
Level,
REPLICATE('--', Level) + EmployeeName AS HierarchyDisplay
FROM EmployeeHierarchy
ORDER BY Level, EmployeeName;
This query produces an indented organization chart starting from top-level managers.
CTEs vs. Temporary Tables or Table Variables
Unlike temporary tables or table variables, CTEs:
- Exist only during query execution
- Don't require explicit cleanup
- Can't have indexes added to them
- Are primarily for improving query structure and readability
Best Practices
- Use meaningful names that describe what the data represents
- Keep individual CTEs focused on a single logical operation
- Comment complex CTEs to explain their purpose
- Consider performance - CTEs are not always more efficient than subqueries
- Avoid excessive nesting - if your query becomes too complex, consider stored procedures or multiple queries
When Not to Use CTEs
CTEs might not be the best choice when:
- You need to reference the same large dataset multiple times (temp tables may be more efficient)
- You need to add indexes for performance optimization
- Your recursive CTE might exceed the default recursion limit (100)
By mastering CTEs, you can write SQL that's not only more maintainable but also easier to understand and debug.