Common Table Expressions


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:

  1. Recursive Queries Using Common Table Expressions.
  2. 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.
  3. 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/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.

Leave a comment

Your email address will not be published. Required fields are marked *

*