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

Introduction

In this article, we going to learn about CTE and Recursive CTE in MS SQL Server and their benefits.

What is CTE?

CTE is created by using "WITH" statement and begin with a semicolon (;)
It is used to hold the temporary result set or result of complex sub-query.
The scope of CTE is limited to the current query.
CTE improves readability and ease in the maintenance of complex queries and sub-queries.

Syntax

;WITH cte_name 
AS
(
   cte_query_definition
)
SELECT * FROM cte_name;

Example

;With StudentCTE
AS
(
    SELECT stu.Id, stu.Name, stu.Age, Addr.Address from Student stu
    INNER JOIN Address Addr ON stu.Id = Addr.Id
)
SELECT * FROM StudentCTE --Using CTE
WHERE StudentCTE.Age > 50
ORDER BY StudentCTE.Id

There are two types of Common Table Expression,

Non-Recursive CTE - It does not have any reference to itself in the CTE definition.
Recursive CTE - When a CTE has reference in itself, then it’s called recursive CTE.

When to use CTE?

This is used to store the result of a complex subquery for further use.
This is also used to create a recursive query.

Recursive CTE

A Recursive CTE is a CTE that references itself. The CTE repeatedly executes, returns subsets of data, until it returns the complete result set.

Syntax

WITH cte_name
AS  
( 
    cte_query_definition (or) initial query  -- Anchor member
    UNION ALL 
    recursive_query with condition     -- Recursive member
)  
SELECT * FROM cte_name

The above syntax has 3 parts,

The cte_query_definition or initial query that returns the base result set of the CTE. It is called an anchor member.
A recursive query that references the CTE. It is called the recursive member. The recursive member is a UNION ALL with the anchor member.
A condition in the recursive member (Ex. Where condition) that terminates the execution of the recursive member.

Recursive CTE's Execution Order

The first step is, execute the initial query (anchor member) which returns the base result set which is used for the next iteration.
The second step is, execute the recursive query with the input result set from the previous iteration. If the condition is met, then return a final result set, otherwise, return the sub-result set.
The third step is, combine above both result sets (base result and sub-result from step 2) using the UNION ALL operator to produce the result set which is assigned to CTE. Now, this result is input to the next iteration. Repeat Step 2 and Step 3 until the condition is met in Step 2.

Example 1

Let's create a recursive CTE that prints from 1 to 100.

;WITH  number_cte
AS  
(
    SELECT 1 AS n -- Anchor member
    UNION ALL
    SELECT n + 1 FROM number_cte -- Recursive member
    WHERE  n < 101 -- Terminate condition
)
SELECT n FROM number_cte;

Example 2

Let's create a recursive CTE that returns weekdays from Monday to Saturday.

;WITH days_cte (n, weekday) 
AS 
(
    SELECT 0, DATENAME(DW, 0)  -- Anchor member
    UNION ALL
    SELECT n + 1, DATENAME(DW, n + 1) FROM days_cte  -- Recursive member
    WHERE n < 6  -- Terminate condition
)
SELECT weekday FROM days_cte;

Example 3

Let's create a recursive CTE that returns the hierarchy of the employee table.

CREATE TABLE dbo.Employee(
    Emp_Id int NOT NULL Primary key,
    Emp_Name nvarchar(50) NOT NULL,
    Emp_Role nvarchar(30) NOT NULL,
    ReportsTo int NULL,
    FOREIGN KEY (ReportsTo) REFERENCES dbo.Employee(Emp_Id)
)

Insert into dbo.Employee values (1, 'Sathya', 'Director', NULL);
Insert into dbo.Employee values(2, 'Kavin', 'Senior Manager', 1);
Insert into dbo.Employee values(3, 'Kayal', 'Senior Manager', 1);
Insert into dbo.Employee values(4, 'Anbu', 'Manager', 2);
Insert into dbo.Employee values(5, 'Naga', 'Manager', 3);
Insert into dbo.Employee values(6, 'Shanthi', 'Lead', 4);
Insert into dbo.Employee values(7, 'Krishna', 'Lead', 5);
Insert into dbo.Employee values(8, 'Prithivi', 'Senior Developer', 6);
Insert into dbo.Employee values(9, 'Swathi', 'Senior Developer', 7);
Insert into dbo.Employee values(10, 'Indu', 'Developer', 8);
Insert into dbo.Employee values(11, 'Vijay', 'Developer', 9);

Let's create a recursive CTE that returns the full hierarchy of the employee table.

;WITH Emp_Cte AS 
( 
--initialization 
SELECT Emp_Id, Emp_Name, ReportsTo  
FROM dbo.Employee 
WHERE ReportsTo IS NULL 
UNION ALL 
--recursive execution 
SELECT e.Emp_Id,e.Emp_Name, e.ReportsTo 
FROM dbo.Employee e INNER JOIN Emp_Cte m  
ON e.ReportsTo = m.Emp_Id 
) 
SELECT * FROM Emp_Cte;

Let's create a recursive CTE that returns the hierarchy of the particular person in the employee table. In the below example, will get the Kavin's (Emp_id is 2) reportees hierarchy.

;WITH Emp_Cte AS 
( 
--initialization 
SELECT Emp_Id, Emp_Name, ReportsTo  
FROM dbo.Employee 
WHERE ReportsTo  = 2
UNION ALL 
--recursive execution 
SELECT e.Emp_Id,e.Emp_Name, e.ReportsTo 
FROM dbo.Employee e INNER JOIN Emp_Cte m  
ON e.ReportsTo = m.Emp_Id 
) 
SELECT * FROM Emp_Cte;

Max Recursion

Consider the below example,

;WITH  number_cte
AS  
(
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM number_cte 
    WHERE  n < 200
)
SELECT n FROM number_cte;

If you run the above query it will show the below error.

“The maximum recursion 100 has been exhausted before statement completion”

The reason behind this error is, by default maximum number of recursion allowed for CTE is 100. If the numbers of recursion in the CTE are more than 100 then will get the error.

We can resolve this error by adding the MAXRECURSION option. MAXRECURSION option used to specify the maximum number of recursions allowed for CTE. The number of recursions is a non-negative integer between 0 and 32767. Here value 0 means there are no limit is applied to the recursion level. This is the risk of an infinite loop with poorly written queries.

;WITH  number_cte
AS  
(
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM number_cte 
    WHERE  n < 250
)
SELECT n FROM number_cte OPTION(MAXRECURSION 250);

Summary

In this article, you have learned following topics,

What is CTE?
Use of CTE
Recursive CTE with Example.
MAXRECURSION Option.