Home » Recursive and Non-Recursive Common Table Expression

Recursive and Non-Recursive Common Table Expression

The Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, DELETE statements. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query.

SQL Server supports two types of CTEs—Recursive and Non-Recursive.

1.A Non-Recursive CTE is one that does not reference itself within the CTE. Non-Recursive CTEs tend to be simpler than Recursive CTEs.

USE AdventureWorks
GO

WITH CTE_Demo AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
)
SELECT * FROM CTE_Demo
GO

2.A Recursive CTE is one that references itself within that CTE. The Recursive CTE is useful when working with Structured/Hierarchical data because the CTE continues to execute until the query returns the entire Results.


USE AdventureWorks
GO

WITH CTE_Rec AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL

UNION ALL

SELECT E.EmployeeID, E.ContactID, E.LoginID, E.ManagerID, E.Title, E.BirthDate
FROM HumanResources.Employee E
INNER JOIN CTE_Rec demo ON demo.EmployeeID = E.ManagerID
)

SELECT *
FROM CTE_Rec OPTION (MAXRECURSION 3)
GO

Note that a CTE created incorrectly could enter an infinite loop. To prevent this, you can include the MAXRECURSION hint in the OPTION clause of the SELECT, INSERT, UPDATE, DELETE, or MERGE statements.

Example:

SELECT CustomerID, PersonID, StoreID
FROM cte
OPTION (MAXRECURSION 2);
GO

Error encountered if Maxrecursion is reached:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.

Leave a Reply

Your email address will not be published. Required fields are marked *