In Microsoft SQL Server 2005 the common table expression (CTE) is a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
The common table expression (CTE), Introduced in Microsoft sql-server-2008, is a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
A CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:
- Create a recursive query.
- Substitute for a view when the general use of a view is not required.
- Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
- Reference the resulting table multiple times in the same statement.
Resources
