This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write common table expressions (CTEs)

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

A Common Table Expression (CTE) is a temporary, named result set that exists only for the duration of a single SQL statement. CTEs simplify complex queries by breaking them into logical, readable components. They can be referenced in SELECT, INSERT, UPDATE, DELETE, and MERGE statements and are particularly useful for hierarchical queries, recursive operations, and improving query readability.

Unlike temporary tables or table variables, CTEs are not physically stored in the database. They are defined using the WITH keyword and exist only during the execution of the immediately following statement.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• What CTEs are
• How to create and use CTEs
• Nonrecursive CTEs
• Recursive CTEs
• Multiple CTEs
• Using CTEs with DML statements
• Recursive query patterns
• Performance considerations
• CTE limitations
• Best practices
• AI-enabled database scenarios

Understanding CTEs is important because they are commonly used in enterprise SQL development to organize complex logic, traverse hierarchical data, and prepare datasets for reporting and AI workloads.

What Is a Common Table Expression?

A Common Table Expression is a temporary named query defined immediately before another SQL statement.

General syntax:

WITH CTE_Name AS
(
    SELECT ...
)
SELECT *
FROM CTE_Name;

The CTE is available only to the statement immediately following its definition.

Benefits of CTEs

CTEs offer several advantages:

• Improve readability
• Simplify complex queries
• Replace deeply nested subqueries
• Enable recursive queries
• Make SQL easier to debug
• Encourage modular query design
• Improve maintainability
• Support DML operations

Creating a Simple CTE

Example:

WITH HighValueOrders AS
(
    SELECT
        OrderID,
        CustomerID,
        TotalAmount
    FROM Sales.Orders
    WHERE TotalAmount > 5000
)
SELECT *
FROM HighValueOrders;

The CTE filters orders before the final query executes.

Referencing a CTE

A CTE behaves similarly to a temporary result set.

Example:

WITH CustomerTotals AS
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
)
SELECT *
FROM CustomerTotals
WHERE TotalSales > 10000;

CTEs and Query Readability

Without a CTE:

SELECT *
FROM
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
) AS SalesTotals;

Using a CTE often makes the query easier to understand, particularly when multiple steps are involved.

Multiple CTEs

Multiple CTEs can be defined within a single WITH clause.

Example:

WITH CustomerTotals AS
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
),
TopCustomers AS
(
    SELECT *
    FROM CustomerTotals
    WHERE TotalSales > 10000
)
SELECT *
FROM TopCustomers;

Each CTE can reference earlier CTEs defined in the same WITH clause.

Recursive CTEs

A recursive CTE repeatedly references itself until a termination condition is met.

It consists of:

• An anchor member
• A recursive member

Example:

WITH Numbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
SELECT *
FROM Numbers;

Result:

1
2
3
4
5
6
7
8
9
10

Recursive CTE Structure

A recursive CTE has two parts:

Anchor member

Returns the initial result.

SELECT 1 AS Number

Recursive member

References the CTE itself.

SELECT Number + 1
FROM Numbers
WHERE Number < 10

The recursion ends when no additional rows are returned.

Hierarchical Queries

Recursive CTEs are ideal for hierarchical data such as:

• Organizational charts
• Employee-manager relationships
• Bill of materials
• Folder structures
• Product categories

Example:

WITH EmployeeHierarchy AS
(
    SELECT
        EmployeeID,
        ManagerID,
        EmployeeName
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName
    FROM HumanResources.Employee e
        INNER JOIN EmployeeHierarchy h
            ON e.ManagerID = h.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;

Using MAXRECURSION

SQL Server limits recursion to 100 levels by default.

To override the limit:

OPTION (MAXRECURSION 500);

Unlimited recursion:

OPTION (MAXRECURSION 0);

Using unlimited recursion should be done cautiously to avoid infinite loops.

CTEs with INSERT

Example:

WITH LargeOrders AS
(
    SELECT *
    FROM Sales.Orders
    WHERE TotalAmount > 10000
)
INSERT INTO Sales.ArchiveOrders
SELECT *
FROM LargeOrders;

CTEs with UPDATE

Example:

WITH CustomerDiscounts AS
(
    SELECT
        CustomerID,
        Discount
    FROM Sales.Customers
    WHERE Discount < 0.05
)
UPDATE CustomerDiscounts
SET Discount = 0.05;

The CTE provides an updateable result set because it references a single base table without disqualifying constructs.

CTEs with DELETE

Example:

WITH OldOrders AS
(
    SELECT *
    FROM Sales.Orders
    WHERE OrderDate < '2023-01-01'
)
DELETE
FROM OldOrders;

CTEs with MERGE

CTEs can simplify complex merge operations.

Example:

WITH UpdatedCustomers AS
(
    SELECT *
    FROM Sales.CustomerImport
)
MERGE Sales.Customers AS Target
USING UpdatedCustomers AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN
    UPDATE
    SET CustomerName = Source.CustomerName
WHEN NOT MATCHED THEN
    INSERT (CustomerID, CustomerName)
    VALUES (Source.CustomerID, Source.CustomerName);

CTEs vs. Subqueries

CTEs vs. Temporary Tables

CTEs vs. Table Variables

Performance Considerations

Although CTEs improve readability, they do not automatically improve performance.

Consider the following:

• CTEs are expanded into the execution plan by the optimizer rather than materialized by default.
• Large CTEs referenced multiple times may be re-evaluated.
• Recursive CTEs can become expensive for deep hierarchies.
• Temporary tables may outperform CTEs for large intermediate result sets reused across multiple statements.
• Proper indexing on underlying tables remains critical.

Always review the execution plan when optimizing complex queries.

CTE Limitations

Developers should understand these limitations:

• Scope is limited to one statement.
• Cannot be referenced by subsequent statements.
• Cannot include an ORDER BY clause unless used with TOP, OFFSET/FETCH, or FOR XML.
• Cannot create indexes on a CTE.
• Recursive CTEs require a termination condition.
• Excessive recursion can impact performance or lead to errors if recursion limits are exceeded.

AI-Enabled Database Scenarios

CTEs are frequently used in AI-enabled database solutions to prepare data before AI processing.

Examples include:

• Cleaning and filtering text before embedding generation
• Building hierarchical product catalogs for Retrieval-Augmented Generation (RAG)
• Preparing conversation histories for prompt construction
• Identifying duplicate records before vectorization
• Aggregating customer interactions for AI analysis
• Transforming datasets before intelligent search indexing
• Organizing graph-like relationships that feed AI models

CTEs provide a readable way to express complex transformations commonly required before AI workflows.

Best Practices

• Give CTEs meaningful names.
• Use CTEs to simplify complex queries.
• Prefer CTEs over deeply nested subqueries.
• Use recursive CTEs only when recursion is required.
• Always include a termination condition in recursive CTEs.
• Test recursive queries with realistic datasets.
• Review execution plans for large queries.
• Consider temporary tables for large reusable intermediate results.
• Keep CTE definitions focused on a single logical task.
• Avoid excessive nesting of multiple CTEs.

Common Exam Tips

For the DP-800 exam, remember these key points:

• CTEs begin with the WITH keyword.
• A CTE exists only for the immediately following statement.
• Recursive CTEs consist of an anchor member and a recursive member.
• Recursive CTEs are commonly used for hierarchical data.
• SQL Server limits recursion to 100 levels by default.
• OPTION (MAXRECURSION n) changes the recursion limit.
• CTEs can be used with SELECT, INSERT, UPDATE, DELETE, and MERGE.
• CTEs are not stored as database objects.
• CTEs improve readability but do not guarantee better performance.
• Recursive CTEs must include a termination condition.

Practice Exam Questions

Question 1

Which keyword is used to define a Common Table Expression?

A. WITH

B. TEMP

C. DEFINE

D. AS

Answer: A

Explanation: Every Common Table Expression begins with the WITH keyword followed by the CTE name and query definition.

Question 2

How long does a Common Table Expression exist?

A. Until the database connection closes

B. Until the current transaction completes

C. Only for the execution of the immediately following SQL statement

D. Until it is explicitly dropped

Answer: C

Explanation: A CTE exists only for the single statement immediately following its definition.

Question 3

Which capability distinguishes a recursive CTE from a nonrecursive CTE?

A. It can reference itself.

B. It creates a permanent table.

C. It automatically creates indexes.

D. It stores intermediate results in tempdb.

Answer: A

Explanation: Recursive CTEs reference themselves to repeatedly process data until a termination condition is reached.

Question 4

Which type of data is most appropriate for a recursive CTE?

A. Monthly sales totals

B. Customer invoices

C. Product pricing

D. Organizational hierarchies

Answer: D

Explanation: Recursive CTEs are commonly used for hierarchical data such as organizational charts, bill of materials, and category trees.

Question 5

Which statement about recursive CTEs is correct?

A. They do not require an anchor member.

B. They require both an anchor member and a recursive member.

C. They can only return numeric data.

D. They cannot use UNION ALL.

Answer: B

Explanation: Every recursive CTE contains an anchor member that produces the initial rows and a recursive member that references the CTE itself.

Question 6

What is the default maximum recursion level in SQL Server?

A. 10

B. 50

C. 100

D. Unlimited

Answer: C

Explanation: SQL Server limits recursive CTE execution to 100 levels by default unless the MAXRECURSION query hint is specified.

Question 7

Which statement correctly describes CTE performance?

A. CTEs always execute faster than temporary tables.

B. CTEs are always materialized into temporary storage.

C. CTEs automatically create indexes.

D. CTEs primarily improve query readability, while performance depends on the execution plan and underlying data.

Answer: D

Explanation: CTEs improve readability and maintainability, but the query optimizer determines how they are executed. They do not inherently improve performance.

Question 8

Which DML operation can use a Common Table Expression?

A. SELECT only

B. SELECT and INSERT only

C. SELECT, INSERT, UPDATE, DELETE, and MERGE

D. UPDATE only

Answer: C

Explanation: CTEs can precede and be referenced by SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

Question 9

When should a temporary table typically be preferred over a CTE?

A. When a readable single-statement query is needed

B. When recursion is required

C. When the intermediate result set must be reused across multiple statements or indexed

D. When querying hierarchical data

Answer: C

Explanation: Temporary tables persist beyond a single statement, can be indexed, and are often more efficient when intermediate results are reused multiple times.

Question 10

How are CTEs commonly used in AI-enabled database solutions?

A. They directly generate vector embeddings.

B. They replace vector indexes.

C. They eliminate the need for application logic.

D. They simplify complex data preparation tasks such as filtering, aggregating, and organizing data before embedding generation, intelligent search, or Retrieval-Augmented Generation (RAG) workflows.

Answer: D

Explanation: CTEs are commonly used to prepare and transform datasets before downstream AI processing, improving readability and maintainability of complex SQL used in AI-enabled database solutions.

Go to the DP-800 Exam Prep Hub main page