- MS Sql Server
- 819
- March-20-2018
- by Rizwan Saqib
Common Table Expression
A common table expression (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. A CTE 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:
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 meta data.
3.
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.
4.Reference the resulting table multiple times in the same statement.
Use OF CTE
Using a CTE offers the advantages of improved readability and easy 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 CTE until the final result set is generated.
Note
CTE can be defined in user-defined routines, such as functions, stored procedures, triggers, or view
