About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Tuesday, 10 May 2011

Prevent recursive CTE from entering an infinite loop


Common Table Expression (CTE) is not a new feature now. It has been around with the launch of SQL Server 2005. Since then, we might have come across many powerful examples of using CTE and have made the most out of it. The best use of CTE I take is through recursion. We can create recursive CTE to get our job done very easily in situations where we need to repeatedly refer a table/view.

The most common situation in which developers land up is an infinite loop for a badly build CTE. So, to counter this situation, we can make use of MAXRECURSION option.
Let’s give it a hands on..



USE AdventureWorks
GO

;WITH EmpCTE
AS
(
  SELECT
    EmployeeID, ManagerID
  FROM
    HumanResources.Employee
  WHERE
    ManagerID IS NULL
  UNION ALL
  SELECT
    e.EmployeeID, e.ManagerID
  FROM
    HumanResources.Employee e
  INNER JOIN
    EmpCTE cte
  ON cte.EmployeeID = e.ManagerID
)
SELECT * FROM EmpCTE OPTION (MAXRECURSION 4)
GO



Now if your CTE goes beyond 4th recursion it will throw an error and stop executing. And if you happen put MAXRECURSION value too low, it might be possible that before your desired result is accomplished it will throw an error.
For example if you change MAXRECURSION to value 3. It will throw following error.
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.

In short, MAXRECUSION is good way to prevent recursive CTE to enter an infinite loop.

No comments:

Post a Comment

Post a Comment