CTE (Common Table Expression)SQL

The Common Table Expressions (CTE) are imported into the SQL to simplify many classes of the Structured Query Language (SQL) for a derived table, which is unsuitable. It was introduced in 2005 SQL SERVER version.

The common table expressions (CTE) are a result set, which we reference with the SELECT, INSERT, UPDATE, or DELETE statement. In SQL 2008, we add a CTE for the unique MERGE statement.

How to use CTEs in T-SQL?

The use of Common Text Expression is to add the clause "WITH" before the SELECT, INSERT, UPDATE, DELETE or MERGE statement. The WITH clause contain one or more CTEs, which are separated by commas.

We generate CTEs when we refer to any table. The CTE result set is not accessible to any statements when we run the particular statement.

Creating a Recursive table expression

The recursive CTE is used when we are working with hierarchical data. An example of hierarchical data in the table is the list of students in the group. For each student, the counter generates a ReferenceID and a NAME. The ReferenceID references itself like an employee ID in a recursive table. We use the CTE to display the position of employee's database.

If the CTE is created wrong, it enters into the infinite loop.

To prevent the endless loop, MAXRECURSION will added in the OPTION clause of the INSERT, DELETE, UPDATE, SELECT or MERGE statement.

Use below code to create a table:

After the Employee table is created, a SELECT statement, which is preceded by a WITH clause that includes a CTE named cteReports is created:

It is an essential tool to generate the inconsistent result set, and retrieved in the SELECT, UPDATE, INSERT, MERGE, or DELETE the statement.