How to Use CTE in SQL Server

Galaxy Glossary

How do I use a CTE in SQL Server?

A Common Table Expression (CTE) defines a temporary, named result set that you can reference within a single SQL Server statement.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What problem does a CTE solve?

CTEs clarify complex queries by isolating subqueries, enabling recursion, and improving readability without creating permanent tables.

How do I write a simple CTE?

Start with WITH, name the CTE, supply an AS clause containing a SELECT, then query the CTE in the outer statement.

WITH RecentOrders AS ( SELECT id, customer_id, order_date, total_amount FROM Orders WHERE order_date >= DATEADD(day,-7,GETDATE()) ) SELECT * FROM RecentOrders;

When should I prefer a CTE over a subquery?

Choose a CTE when the interim result is reused, when recursion is needed, or when breaking a multi-step calculation into readable blocks.

How can I build a recursive CTE?

Anchor the first SELECT, UNION ALL to the recursive member, and reference the CTE inside itself.

WITH StockLevels AS ( SELECT id, name, stock, 1 AS lvl FROM Products WHERE id = 42 UNION ALL SELECT p.id, p.name, p.stock, lvl+1 FROM Products p JOIN StockLevels s ON p.id = s.id WHERE p.stock < 10 ) SELECT * FROM StockLevels;

Can I reference multiple CTEs in one query?

Yes.Chain them with commas, then query any defined name in the final SELECT.

WITH BigSpenders AS ( SELECT customer_id, SUM(total_amount) AS spend FROM Orders GROUP BY customer_id HAVING SUM(total_amount) > 1000 ), LastContact AS ( SELECT id, MAX(created_at) AS last_seen FROM Customers GROUP BY id ) SELECT c.name, b.spend, l.last_seen FROM Customers c JOIN BigSpenders b ON c.id=b.customer_id JOIN LastContact l ON c.id=l.id;

Best practices for CTEs?

Keep each CTE focused, index the underlying tables, avoid over-nesting, and always examine execution plans with SET STATISTICS.

.

Why How to Use CTE in SQL Server is important

How to Use CTE in SQL Server Example Usage


WITH CustomerTotals AS (
    SELECT c.id, c.name, SUM(o.total_amount) AS lifetime_value
    FROM Customers c
    JOIN Orders o ON o.customer_id=c.id
    GROUP BY c.id, c.name
),
Top10 AS (
    SELECT *
    FROM CustomerTotals
    ORDER BY lifetime_value DESC
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
)
SELECT t.*, COUNT(oi.id) AS item_count
FROM Top10 t
JOIN Orders o ON o.customer_id=t.id
JOIN OrderItems oi ON oi.order_id=o.id
GROUP BY t.id, t.name, t.lifetime_value;

How to Use CTE in SQL Server Syntax


WITH cte_name [ (column_alias [, ...]) ]
AS (
    non-recursive | recursive SELECT statement
)
[, additional_cte AS ( ... )]
<main_query_using_cte>

Options & notes:
• Column aliases override inferred names.
• Multiple CTEs are comma-separated.
• Recursive CTEs use UNION ALL between anchor and recursive members.
• CTE scope ends with the batch or GO delimiter.

Common Mistakes

Forgetting the outer query: Defining a CTE without a following SELECT, INSERT, UPDATE, or DELETE raises "Incorrect syntax near ';'." Add an outer statement right after the CTE.
Ending the batch too early: Placing GO between the CTE and its query resets scope, causing "Invalid object name". Keep the CTE and its consumer in the same batch.

Frequently Asked Questions (FAQs)

Is a CTE stored in tempdb?

SQL Server builds the CTE result in memory or spills to tempdb if needed, similar to derived tables. It is not persisted after the batch ends.

Does a CTE improve performance?

Performance is usually the same as an equivalent subquery. The gain is readability; check execution plans to confirm cost.

Can I update through a CTE?

Yes. Use UPDATE cte_alias SET ... FROM cte_name, but ensure the CTE selects from the target table directly.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie