Programming Microsoft® SQL Server™ 2008

Common Table Expressions

A common table expression (CTE) closely resembles a nonpersistent view. It is a temporary named result set that you define in your query and that will be used by the FROM clause of the query. Each CTE is defined only once (but can be referred to as many times as necessary while still in scope) and lives for as long as the query lives. You can use CTEs to perform recursive operations. Here is the syntax to create a CTE:

WITH <name of your CTE>(<column names>)
AS
(
<actual query>
)

SELECT * FROM <name of your CTE>

Note

Many of our examples in this chapter use the AdventureWorks2008 sample database. To run these examples, you ...