SQL Server Interview Question #76

What is a Common Table Expression (CTE)?

Joins, Subqueries, CTEs & Set Operators Mid-Level Intermediate

Detailed Explanation

A Common Table Expression is a named query expression defined with WITH and referenced by the statement immediately following it.

CTEs can improve readability by breaking a complex query into logical parts. They are not automatically materialized temporary result sets; the optimizer determines how the query is executed. A non-recursive CTE generally exists only for the scope of the following statement.

Code Example

WITH ActiveProducts AS
(
    SELECT Id, Name, Price, CategoryId
    FROM dbo.Products
    WHERE IsActive = 1
)
SELECT Id, Name, Price
FROM ActiveProducts
WHERE Price >= 100;