Common Table Expressions (CTE) in SQL

A Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE can only be referenced by the statement that immediately follows it. A CTE allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

A CTE can also be thought of a disposable view. No indexes or additional statistics are stored for a CTE, and it functions as a shorthand for a sub-query.

A CTE is not stored as an object and lasts only for the duration of the query. So, it is similar to a derived table. However, unlike a derived table, a CTE can be self-referencing (recursive CTE) and referenced multiple times in the same query.

A CTE can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

When could a Common Table Expression be used?

A CTE could be used to fulfill the following requirements:

• To create a recursive query.
• When the general use of a view is not required, a CTE can be substituted for a view; that is, you do not have to store the definition in metadata.
• A CTE can be used to enable grouping by a column that is derived from a scalar sub-select, or a function that is either not deterministic or has external access.
• A CTE can be used to reference the resulting table multiple times in the same statement.

Structure of a CTE

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

AS

( CTE_query_definition )

SELECT <column_list>

FROM expression_name;

Example

;WITH WebsiteDetails AS -- notice the semicolon here

(

SELECT ROW_NUMBER() OVER (ORDER BY in_website_id) AS [No.],

in_website_id, vc_website_domain_name

FROM dbo.website

WHERE bt_active = 1

)

SELECT * FROM WebsiteDetails

Note: The semicolon before WITH is used basically to terminate previous SQL statements to avoid errors.