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 of CTE

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 of CTE

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.

Create CTE in SQL Server

Create Table
Insert Table Value.
CTE example,