SQL Server CTE

CTE : A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH ExampleCTE (id, fname, lname)

AS

(

SELECT id, firstname, lastname FROM table

)

SELECT * FROM ExampleCTE

Common Table Expression Syntax:

A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

The query using the CTE must be the first query appearing after the CTE.

A CTE can be used in place of a view in some instances.

A CTE can be used to:

• Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

• 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.

• 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.

Query Representation of CTE :