Importance of MAXRECURSION option with CTE (Common Table Expression) - SQL Server 2005

You know very well about CTE and also I wrote about the MAXRECURSION option with CTE when we have hierarchy depth level greater than 100 and due to that error is raised in the previous post. So we need to define MAXRECURSION 0 option there.

But sometimes it is also creating an issue because of the data and due to that it is going into an infinite loop and query execution never end. Means query will found parent and child are same or creating a circular chain which never complete the execution. Here I would like to share my experience which I faced many times and what I have made to resolve it. Let's describe my experience with small example.

IF ( Object_id('CTEMaxLevelTab') > 0 )
  DROP TABLE CTEMaxLevelTab

GO

CREATE TABLE CTEMaxLevelTab
  (
     ChildId   BIGINT IDENTITY(1, 1)
     ,ParentId BIGINT
  )

GO

INSERT INTO CTEMaxLevelTab
            (ParentId)
SELECT TOP 5
  NULL
FROM   sys.sysobjects a
       CROSS JOIN sys.sysobjects b

GO

UPDATE CTEMaxLevelTab
SET  ParentId = ChildId - 1

GO

Let us update one of ParentId and update to same as ChildId.

UPDATE CTEMaxLevelTab
SET  ParentId = 1
where ChildId = 1

SELECT 
* 
FROM CTEMaxLevelTab

Now we use the MAXRECURSION 0 option to get hierarchical data, And see what happens here.

;WITH cteLevels
AS
(
SELECT
  ChildId   AS Child
  ,ParentId AS Parent
  ,1        AS [Level]
FROM   CTEMaxLevelTab 
WHERE CHILDID = 1


UNION ALL

SELECT
  ChildId      AS Child
  ,ParentId    AS Parent
  ,[Level] + 1 AS [Level]
FROM   CTEMaxLevelTab
       INNER JOIN cteLevels
         ON ( ParentId = Child ) 

)


SELECT
  *
FROM   cteLevels
OPTION (MAXRECURSION 0); 

You have an idea hat happened here when we define MAXRECURSION with 0 in the script, 0 means max or unlimited hierarchy depth level.

The resolution of this issue is we should define MAXRECURSION with limited hierarchy level. We have maximum 5 levels in table data, so we can define as MAXRECURSION 5. In this way it will raise an error of CTE, but never go in infinite loop or not create circular chain. This is the revised script should we use.

;WITH cteLevels
AS
(
SELECT
  ChildId   AS Child
  ,ParentId AS Parent
  ,1        AS [Level]
FROM   CTEMaxLevelTab 
WHERE CHILDID = 1


UNION ALL

SELECT
  ChildId      AS Child
  ,ParentId    AS Parent
  ,[Level] + 1 AS [Level]
FROM   CTEMaxLevelTab
       INNER JOIN cteLevels
         ON ( ParentId = Child ) 

)


SELECT
  *
FROM   cteLevels
OPTION (MAXRECURSION 5); 

This is just what i want to share. The resolution is always coming out through our experience!