What is Common Table Expressions(CTE)

A common table expression 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. A CTE(common table expression) 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(common table expression) can be self-referencing and can be referenced multiple times in the same query.

A CTE have following Uses:

1. Create a recursive query.
2. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
3. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
4. Reference the resulting table multiple times in the same statement.

Using a CTE(common table expression) offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs(common table expressions) until the final result set is generated.

CTEs(common table expressions)  can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;

Example

-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO