SQL Server 2014 Development Essentials

Common Table Expressions

A common table expression (CTE) is a temporary result set that your query can reference. You can use a CTE just as you would any other table. However, when the query ends, the CTE is deleted from the memory. We also use CTEs to create recursive queries, simplify complex query logic, and create multiple references of the same table.

To create a CTE, use a WITH clause outside the SELECT statement. The following is the basic syntax of a CTE:

WITH cte_name ([(column_name [,...n])])
AS
(CTE_query_definition)

The following is an explanation of the arguments of the CTE syntax:

• cte_name: This is the name of the CTE you have referenced in the query
• column_name: This is the name of the column; note that it is an optional argument

The ...