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

But sometime it is also creating issue because of the data and due to that it is going in infinite loop and query execution never end. Means query will found parent and child are same or creating circular chain which never complete the execution.

Here i would like to share my experience which i faced many times and what i have did 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 happen 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 limied hierarchy level. We have maximum 5 levels in table data, so we can define as MAXRECURSION 5.

At this way it will raise 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); 

The solution is always come out through our experience!