SQL Server CTE statement Varbinary(MAX) Not Returning the RootID

Question

I have a hierarchy table to store a binary tree. I'm using a recursive query to retrieve the tree level based on the IntroducerID as the "RootID". The value returned as what I expected, but after the AgentId number increment reached 116, this CTE query doesn't return the Level value in hierarchical form.

Like it cannot trace the RootID anymore.

This is what my table structure looks like;

CREATE TABLE [dbo].[TblHierarchy]
(
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [AgentID] [bigint] NULL,
      [AgentName] [varchar](50) NULL,
      [RootID] [bigint] NULL,
      [IntroducerID] [bigint] NULL,
      [Description] [varchar](50) NULL,
      [HierarchyTree] [hierarchyid] NULL
) ON [PRIMARY]

Below is the sample data from the table;

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (1, 1, N'Toh', 0, NULL, N'', NULL)

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (2, 2, N'Man', 1, 0, N'Child of Toh', NULL)

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (3, 3, N'Rul', 1, 0, N'Child of Toh', NULL)

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (4, 4, N'Rafiq', 2, 2, N'Child of Man', NULL)

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (5, 5, N'Paan', 2, 2, N'Child of Man', NULL)

And this is the query to retrieve the info.

WITH CTE AS
(
    SELECT 
        H1.AgentID,
        H1.RootID,
        H1.AgentName,
        H1.Description [Parent],
        Description [Self Description],
        CAST(AgentID AS varbinary(MAX)) [Level],
        CAST (h1.AgentID AS varchar(max)) [LevelID],
        CAST (H1.IntroducerID AS varchar(max)) [IntroducerID]
    FROM 
        TblHierarchy H1
    WHERE 
        H1.RootID = 3

    UNION ALL 

    SELECT 
        H2.AgentID,
        H2.RootID,
        H2.AgentName,
        c.[Self Description],
        Description [Self Description],
        c.[Level]+CAST(h2.AgentID AS varbinary(MAX)) AS [Level] ,
        c.[LevelID] + '>' + CAST (h2.AgentID AS varchar(max)) [LevelID],
        CAST (H2.IntroducerID AS varchar(max)) [IntroducerID]
    FROM
        TblHierarchy H2
    INNER JOIN 
        CTE c ON h2.RootID = c.AgentID
)
SELECT *
FROM CTE 
CROSS APPLY 
    (SELECT 
         SUBSTRING(LevelID, 1, CHARINDEX('>', LevelID + '>') - 1) ) c(RootLevelID) 
ORDER BY 
    [Level] DESC
OPTION (MAXRECURSION 0) 

Answer 1

I do not fully understand your needs (especially what you are trying to achieve with all this casting...), but check this out:

EDIT: After some thinking I hope I got it

I removed my previous suggestion, where I had to change your input data. The following uses your input data unchanged and links via AgentID and RootID.

CREATE TABLE [dbo].[TblHierarchy](
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [AgentID] [bigint] NULL,
      [AgentName] [varchar](50) NULL,
      [RootID] [bigint] NULL,
      [IntroducerID] [bigint] NULL,
      [Description] [varchar](50) NULL,
      [HierarchyTree] [hierarchyid] NULL
      ) ON [PRIMARY];


INSERT [dbo].[TblHierarchy] ( [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES 
( 1, N'Toh', 0, NULL, N'', NULL)
,( 2, N'Man', 1, 0, N'Child of Toh', NULL)
,( 3, N'Rul', 1, 0, N'Child of Toh', NULL)
,( 4, N'Rafiq', 2, 2, N'Child of Man', NULL)
,( 5, N'Paan', 2, 2, N'Child of Man', NULL)

SELECT * FROM dbo.TblHierarchy;

WITH recCTE AS
(
    SELECT h.ID
          ,h.AgentID
          ,h.AgentName
          ,h.RootID
          ,h.IntroducerID
          ,h.[Description]
          ,CAST(h.[Description] AS VARCHAR(MAX)) AS HierDescr
    FROM dbo.TblHierarchy AS h WHERE h.IntroducerID IS NULL

    UNION ALL

    SELECT h.ID
          ,h.AgentID
          ,h.AgentName
          ,h.RootID
          ,h.IntroducerID
          ,h.[Description]
          ,r.HierDescr + '/' + h.[Description]
    FROM recCTE AS r
    INNER JOIN dbo.TblHierarchy AS h ON h.RootID=r.AgentID
)
SELECT * FROM recCTE
GO
DROP TABLE [dbo].[TblHierarchy];