Common Table Expression In SQL Server

Introduction

While working with database data, there might appear need to operate over a set of data that does not inherently exist within the system.

What Is CTE (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. (Source: MSDN).

Syntax

With expression_name (columnname1, columnname2)
(
Cte query defination
)

Select columnname from expression_name

Advantages

• Can be used to create a recursive query.
• Can be substituted for a view.
• Allow grouping by a column which might be derived from a scalar subset.
• Can reference itself multiple times.

Disadvantages

• CTE’s members cannot use the following clauses of keywords Distinct, Group By, Having, Top, Joins limiting by this type of the queries that can be created and reducing their complexity.
• The Recursive member can refer to the CTE only once.
• Table Variables and CTE’s cannot be passed as parameters in stored procedures.

Here steps are to create CTE in SQL Server,

1. Create Table
   
   
   
   
2. Insert Table Value.
   
   
   
   
3. CTE example,