CTE (Common Table Expression) and Recursive CTE in SQL Server

Introduction

In this article, you will learn about the common table expression or CTE in SQL Server using the WITH clause.

The common table expression (CTE) is a temporary named result set which is created during query execution and delete once after the query executes. CTE is a temporary table\virtual table with columns and records created during query execution. This can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Why do we use CTE in SQL Server?

• Simplifies your code
• Improves the readability of an SQL query
• The SQL WITH clause allows you to write recursive queries
• Can use to CREATE a view as part of the view's SELECT query.
• It can be used inside the SELECT, INSERT, UPDATE, and DELETE types of queries.

CTEs can be a useful tool when you need to generate temporary result sets that can be accessed in a SELECT, INSERT, UPDATE, DELETE, or MERGE statement.

Below is the common Syntax of CTE.

WITH expression_name (columnname1, columnname2)
AS
(
CTE query definition
)
SELECT columnname FROM expression_name

Common Table Expression Types

• Non-Recursive CTEs: A non-recursive CTE is one that does not reference itself within the CTE. Nonrecursive CTEs tend to be simpler than recursive CTEs
• Recursive CTEs: The recursive CTE is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy.

Now let's create a recursive SQL Server Common Table Expression; lets's create an employee table to demonstrate how recursive CTE works.

CREATE TABLE dbo.Employees
(
  Employee_ID INT NOT NULL PRIMARY KEY,
  First_Name VARCHAR (50) NOT NULL,
  Last_Name VARCHAR (50) NOT NULL,
  Manager_ID INT NULL
)
GO

INSERT INTO Employees VALUES (1, 'Ray', 'Menon', NULL)
INSERT INTO Employees VALUES (2, 'Reshu', 'Dutt', 1)
INSERT INTO Employees VALUES (3, 'Ansh', 'Raj', 1)
INSERT INTO Employees VALUES (4, 'Boby', 'Jain', 2)
INSERT INTO Employees VALUES (5, 'Gargi', 'Jain', 2)
INSERT INTO Employees VALUES (6, 'Aman', 'Mittal', 3)
INSERT INTO Employees VALUES (7, 'Anas', 'Ahamed', 3)
INSERT INTO Employees VALUES (8, 'Dia', 'Mehra', 5)
INSERT INTO Employees VALUES (9, 'Mehak', 'Singh', 5)
INSERT INTO Employees VALUES (10, 'Tara', 'Mehta', 6)
GO

After the employee's table is created, we will demonstrate how recursive CTE works with a select query

Recursive CTE Example

WITH CTE_Employees (Emp_ID, First_Name, Last_Name, Mgr_ID, Emp_Level)
AS
(
	SELECT Employee_ID, First_Name, Last_Name, Manager_ID, 1
	FROM Employees WHERE Manager_ID IS NULL
	UNION ALL
	SELECT e.Employee_ID, e.First_Name, e.Last_Name, e.Manager_ID, ce.Emp_Level + 1
	FROM Employees e INNER JOIN CTE_Employees ce
	ON e.Manager_ID = ce.Emp_ID
)
	SELECT First_Name, Last_Name, Emp_Level,Mgr_ID
	FROM CTE_Employees 
	ORDER BY Emp_Level, Mgr_ID

As you can see above

• CTE returns hierarchical data, and the highest level of the hierarchy is 1, the next level is 2, and then 3, and so on.
• The CTE query has two SELECT statements and is connected with the UNION ALL operator. A recursive CTE query must contain at least two statements and be connected by UNION ALL, UNION, INTERSECT, or EXCEPT operator.

Let's create a simple recursive CTE that prints numbers from 1 to 9.

Recursive CTE Example

WITH CTE_Num
AS 
(
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 FROM CTE_Num
    WHERE Num < 10
)
	SELECT Num FROM CTE_Num WHERE Num < 10

Now let's create another recursive CTE to calculate the factorial.

Recursive CTE Example

WITH CTE_Factorial (N, Factorial) 
AS
(
	SELECT 1, 1 
	UNION ALL   -- here is where it gets recursive
	SELECT N + 1, (N + 1) * Factorial FROM CTE_Factorial
	WHERE N < 10
)
	SELECT N , Factorial FROM CTE_Factorial;

Now let's generate numbers between 1 to 200 by using a recursive CTE by the following script.

Recursive CTE Example

WITH CTE_Num
AS 
(
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 FROM CTE_Num
    WHERE Num < 200
)
	SELECT * FROM CTE_Num;

The above screenshot shows CTE has a default maximum recursion level of 100. Now let's see how to change the MAXRECURSION level.

The below script is used to change the MAXRECURSION level.

Recursive CTE Example

WITH CTE_Num
AS 
(
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 FROM CTE_Num
    WHERE Num < 200
)
	SELECT * FROM CTE_Num
	OPTION (MAXRECURSION 200)

The above screenshot shows how to change the CTE's default maximum recursion level.

Now let's consider the Employees and Departments tables again to understand Common Table Expression more.

SELECT * FROM Employees;

SELECT * FROM Departments;

Let's use the Window function (i.e. Ranking) to understand the CTE more.

We will use the RANK () function here to get department-wise salary rank.

Recursive CTE Example

WITH Dept_Salary_Rank 
AS
(
	SELECT First_Name, Last_Name, Department_id, Salary, 
	RANK () OVER (
	PARTITION BY Department_id ORDER BY Salary) Dept_Salary_Rank
	FROM Employees
)
	SELECT First_Name, Last_Name, r.Department_id, Salary FROM Dept_Salary_Rank r
	INNER JOIN departments d ON d.department_id = r.department_id
	WHERE Dept_Salary_Rank = 1;	

Here is the department-wise salary rank.

In the above screenshot, we can see the department-wise salary rank.

Now we will execute the complete CTE and see the output.

As you can see in the above screenshot, we got dept wise lowest salary.

Now we want to get an overall salary rank irrespective of the department, so we use the below CTE script.

Recursive CTE Example

WITH Salary_Rank 
AS
(
	SELECT First_Name, Last_Name, Salary, 
	RANK() OVER (ORDER BY Salary) Salary_Rank
	FROM Employees
)
	SELECT * FROM Salary_Rank

Now let's create CTE with a DELETE query using the RANK () function.

Recursive CTE Example

WITH Del_Salary_Rank 
AS
(
	SELECT First_Name, Last_Name, Salary, 
	RANK() OVER (ORDER BY Salary) Del_Salary_Rank
	FROM Employees
)
	DELETE FROM Del_Salary_Rank WHERE Salary > = 50000

Summary

As you can see, CTE is a very useful feature in SQL Server. They simplify the code and can also be used in recursive operations. We have covered a variety of examples to use recursive CTE. Hope you find the article useful. Please share your feedback in the comments section.