What is CTE (Common Table Expressions)
A common table expression (CTE) is a temporary result set accessible within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
CTE does not hold any physical space in database like a derived table. But Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
We can also create Nested CTE to staging our data throughout multiple CTE’s
Below are the scenarios where a CTE can best fit:
- Recursive Queries Using Common Table Expressions.
- Instead of creating a view for only one query we can choose CTE. As like view it will not take create it’s definition inside metadata.
- Making queries readable (For complex and long queries)
Advantages : –
Use of CTE offers improved readability and ease in maintenance of complex queries. The query can be divided into blocks and these blocks can then be used to build more complex, interim CTEs until the final result set is generated. Recursion using CTE’s is one of the most useful feature of CTE’s.
Rate This
[Total: 0 Average: 0]
