Using hierarchyId in Parent Child relation

Question

I have a table named tb_Section. It is designed to support n-level hierarchy. Each row has a ParentSetionId that points to another row's SectionId in case of a Parent/Child relationship.

Now, I have a process where I have to copy these sections one by one to another table keeping the hierarchy intact. I arranged the section by Using order by as follows:

Select 
 s.SectionId,
 s.ParentSectionId,
 s.SectionName
From 
 tb_Section s
Where
 s.TemplateId = 123
Order By
 Convert(hierarchyid, '/' + cast(sec.ParentSectionId as varchar)+ '/')

I insert these in a TempTable and Insert each section one by one in another table. This was working fine. But after the application went live an issue was reported.

A user firstly created all the sections as Parent Sections and then one by one edited them and made them child of other sections(Used the most difficult way :/) The section hierarchy was as follows:

Super-Parent
      Parent1
          Child1
          Child2
      Parent2
          Child3
          Child4

The above query in this case returns the following rows in order.

SuperParent
Child1
Child2
Parent1
Child3
Child4
Parent2

Instead of the normal case, which should be this

SuperParent
Parent1    
Child1
Child2
Parent2
Child3
Child4

Any help regarding this? I can't seem to find the reason why this is happening.

UPDATE

Here is the SQL Script where Children are inserted first.

CREATE TABLE tb_Section(
   SectionId BIGINT  NOT NULL PRIMARY KEY 
  ,ParentSectionId BIGINT 
  ,SectionName NVARCHAR(50) NOT NULL
);
INSERT INTO tb_Section(SectionId,ParentSectionId,SectionName) VALUES (SectionId,ParentSectionId,N'SectionName');
INSERT INTO tb_Section(SectionId,ParentSectionId,SectionName) VALUES (686772,686776,N'Child 1');
INSERT INTO tb_Section(SectionId,ParentSectionId,SectionName) VALUES (686773,686776,N'Child 2');
INSERT INTO tb_Section(SectionId,ParentSectionId,SectionName) VALUES (686774,686777,N'Child 3');
INSERT INTO tb_Section(SectionId,ParentSectionId,SectionName) VALUES (686775,686777,N'Child 4');
INSERT INTO tb_Section(SectionId,ParentSectionId,SectionName) VALUES (686776,686778,N'Parent 1');
INSERT INTO tb_Section(SectionId,ParentSectionId,SectionName) VALUES (686777,686778,N'Parent 2');
INSERT INTO tb_Section(SectionId,ParentSectionId,SectionName) VALUES (686778,NULL,N'Super-Parent');

Here is the result my above query returns with hierarchyId

+-----------+-----------------+---------------+--------------+----------------+
| SectionId | ParentSectionId | SectionTypeId | SectionName  |   hierarchy    |
+-----------+-----------------+---------------+--------------+----------------+
|    686778 | NULL            |             1 | Super-Parent | NULL           |
|    686772 | 686776          |             2 | Child 1      | 0xF800298C6620 |
|    686773 | 686776          |             2 | Child 2      | 0xF800298C6620 |
|    686774 | 686777          |             2 | Child 3      | 0xF800298C6660 |
|    686775 | 686777          |             2 | Child 4      | 0xF800298C6660 |
|    686776 | 686778          |             2 | Parent 1     | 0xF800298C66A0 |
|    686777 | 686778          |             2 | Parent 2     | 0xF800298C66A0 |
+-----------+-----------------+---------------+--------------+----------------+

You can see that the hierarchy is different, here child items are showing before the Parents. I hope this sample data helps.

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Mar 15 '16 at 13:15
why not use a hierachid type https://msdn.microsoft.com/en-us/library/bb677173.aspx — Steve Ford, Mar 15 '16 at 13:37
Possible duplicate of [Select statement to return parent and infinite children](http://stackoverflow.com/questions/25550850/select-statement-to-return-parent-and-infinite-children) — Tanner, Mar 15 '16 at 13:44
@SteveFord I am using the hierarchyId in my order by statement, but the hierarchyId only works properly when data is inserted in a tree like form. Here leaf nodes are added first as a Parent nodes and later changed to become leaf-nodes. — Zunair Zubair, Mar 15 '16 at 13:49

Steve Ford · Accepted Answer · 2016-03-15T15:49:15.290

Try using a recursive CTE and create your own hierarchy string:

;WITH Recurs
AS
(
    SELECT SectionId, ParentSectionId, 
           SectionName, '/' + CAST(SectionId AS VARCHAR(MAX)) as Level
    FROM tb_Section
    WHERE ParentSectionId Is NULL
    UNION ALL
    SELECT s.SectionId, s.ParentSectionId, s.SectionName, 
           CAST(r.level + '/' + CAST(s.SectionId AS VARCHAR(MAX) )AS Varchar(MAX))
    FROM tb_Section s
    INNER JOIN recurs r
        ON S.ParentSectionId = r.SectionId
)
SELECT *
FROM Recurs
ORDER BY Level

Produces:

SectionId   ParentSectionId SectionName  Level
686778      NULL            Super-Parent /686778
686776      686778          Parent 1     /686778/686776
686772      686776          Child 1      /686778/686776/686772
686773      686776          Child 2      /686778/686776/686773
686777      686778          Parent 2     /686778/686777
686774      686777          Child 3      /686778/686777/686774
686775      686777          Child 4      /686778/686777/686775

Thanks. This works fine. But in terms of large data, wouldn't CTE be slow? — Zunair Zubair, Mar 16 '16 at 08:46
@ZunairZubair it may be slow for large queries, if you know there are only ever going to be 3 levels then you could accomplish the same with JOINS and UNIONS — Steve Ford, Mar 16 '16 at 09:14
I have n-level hierarchy. But its working fine at the moment. — Zunair Zubair, Mar 16 '16 at 14:26

score 1 · Answer 2 · answered Mar 15 '16 at 17:20

From the data that you've provided, the HierarchyID value looks incorrect to me. When I run this query:

CREATE TABLE tb_Section(
   SectionId BIGINT  NOT NULL PRIMARY KEY 
  ,ParentSectionId BIGINT 
  ,SectionName NVARCHAR(50) NOT NULL
  , h HIERARCHYID
);

INSERT  INTO [tb_Section]
        ( [SectionId], [ParentSectionId], [SectionName], [h] )
VALUES  ( 686778, NULL, 'Super-Parent', NULL ),
        ( 686772, 686776, 'Child 1', 0xF800298C6620 ),
        ( 686773, 686776, 'Child 2', 0xF800298C6620 ),
        ( 686774, 686777, 'Child 3', 0xF800298C6660 ),
        ( 686775, 686777, 'Child 4 ', 0xF800298C6660 ),
        ( 686776, 686778, 'Parent 1 ', 0xF800298C66A0 ),
        ( 686777, 686778, 'Parent 2 ', 0xF800298C66A0 );

SELECT [ts].[SectionId] ,
       [ts].[ParentSectionId] ,
       [ts].[SectionName] ,
       [ts].[h].ToString()
FROM [dbo].[tb_Section] AS [ts]
ORDER BY h

The values for the "Child N" values look don't show any connection back to the Super-Parent. So let's run the following update:

UPDATE  [dbo].[tb_Section]
SET     [h] = CONCAT('/686778', h.ToString())
WHERE   [SectionName] LIKE 'Child%'

We're getting better, but now if we run the select from the previous step again, the results are coming back based on hierarchy depth. That is, the Super-Parent is coming back first, then the Parents, and then finally the children. Which, when you look at the string value of the hierarchy still makes sense; all of the Parent rows have the same hierarchy value and so they'll be sorted together.

One final update should get you to where you want to go:

UPDATE  [dbo].[tb_Section]
SET     [h] = CONCAT(ISNULL(h.ToString(), '/'), [SectionId], '/')

Now, when you run that same select again, the sub-hierarchies are sorted together. So the TL;DR on this one is that the HierarchyID needs to provide a full path from the source (in your case, Super-Parent) down to destination (in your case, Child), inclusive.

Using hierarchyId in Parent Child relation

2 Answers2