HierarchyID--multiple root values in one table

Question

Currently I am trying to work my way around the HierarchyID concept in order to convert a few tables for our programmers. I have the gist of it understood, but one of the cases I'm working on has me stumped.

One particular table deals with different kinds of equipment failure codes using a parent/child relationship model. The problem is, the way the codes are laid out means that the (seemingly) most obvious solution would be to use multiple different trees for each base category. While the CLR methods work for single-root cases, using multiple root values throws off the methods (for example, getting the ancestor of one particular node/ID will also get the values in the same general spot for the other trees).

For reference, the table code:

CREATE TABLE FailCode
(
  IDOrg hierarchyid,
  ID int IDENTITY,
  Name nvarchar(200),
  ParentID int
CONSTRAINT PK_FailCode_ID
  PRIMARY KEY CLUSTERED (ID)
);
GO

I'm looking at my options.
I've figured one option would be to use a dummy root value and have the categories as child nodes of the dummy root. My other idea would be to create a separate table for each category on its own and use a single-root hierarchy for each table.

Beyond that, I'm drawing blanks. My question is:
What other options are there to represent multiple-root hierarchy setups in SQL Server?

EDIT: I should probably add this--the table in question used to have another column for each category, which I had used in my queries. This column is being struck from the table at the behest of another DBA and a couple of programmers, so it is no longer an option for me.

---

(Revised question begins here; I have left the original for the sake of anyone reading.)

@Avarkx:
I humbly thank you for the answer. Unfortunately, there's a bit of a problem--I spoke with the other DBA and he insisted that there have to be three separate root nulls (listed as NULL with parent ID AND having just '/' for the ToString representation).

The table currently looks something like this (sorted by level for ease of reference):

       IDOrg   ID   Name    ParentID
---------------------------------
/      0x       1   Root1   NULL
/      0x       2   Root2   NULL
/      0x       3   Root3   NULL
/1/    0x58     4   P1C1    1
/1/    0x58     9   P2C1    2
/1/    0x58     13  P3C1    3
/1/1/  0x5AC0   11  P9C1    9
/1/1/  0x5AC0   15  P13C1   13
/1/1/  0x5AC0   7   P4C1    4
/1/2/  0x5B40   8   P4C2    4
/1/2/  0x5B40   16  P13C2   13
/1/2/  0x5B40   12  P9C2    9
/2/    0x68     14  P3C2    3
/2/    0x68     10  P2C2    2
/2/    0x68     5   P1C2    1
/2/1/  0x6AC0  17   P5C1    5
/2/1/  0x6AC0  21   P10C1   10
/2/1/  0x6AC0  23   P14C1   14
/2/2/  0x6B40  24   P14C2   14
/2/2/  0x6B40  22   P10C2   10
/2/2/  0x6B40  18   P5C2    5
/3/    0x78    6    P1C3    1
/3/1/  0x7AC0  19   P6C1    6
/3/2/  0x7B40  20   P6C2    6  

So I suppose I need to revise my original question: Is it possible to use this table setup and manipulate queries in order to differentiate between specific trees when retrieving information regarding the hierarchy?
For example, if I needed to get the ancestor for '19', would it be possible to filter things out by tree in a query (or perhaps a query within a stored proc.) so that I just get '6' and not '10' and '14' as well? Adding records is simple enough, but queries like this example are problematic.

(If the answer is "no", I suppose I'll have to work something out with him...that adjacency pair solution is downright brilliant, and I see no reason why we couldn't use that if we weren't willing to do so.)

Answer 1

The dummy root is probably the right track, except you don't actually have to create a dummy root to make multiple roots. Your table also appears to be simultaneously an adjacency pair as well, so you've got a couple of immediate options on how you want to go using the table. Both should probably be tested for suitability, as I have a sneaky feeling that depending on read-a-lot or write-a-lot usage, your mileage from either case may vary.

Since the needs of both your developers and fellow DBAs must be considered, most of the other options are probably out of the question ( though there are loads and loads ). This SQL Fiddle illustrates a couple of ways to leverage the materialized paths inherit in your structure, first through leveraging a multiple-root HIERARCHYID, then through adjacency pair traversal through recursive CTEs.

To implement multiple roots, the only real "trick" to it is to preserve the sub-sequence on the root level. Instead of simply calling NULL descendants during root node creation, like so,

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( NULL, NULL ), '1' );

each root node can be created using the root as the base level ( though the first one obviously doesn't matter ):

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
            SELECT  MAX( IDOrg )
            FROM    dbo.FailCode
            WHERE   IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ),  '1' );

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
            SELECT  MAX( IDOrg )
            FROM    dbo.FailCode
            WHERE   IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ),  '2' );

If you're in more of an UPDATE situtation, you can create the multiple root nodes off the primary key, if you like:

SELECT  CAST( '/' + CONVERT( VARCHAR( 16 ), ID ) + '/' AS HIERARCHYID ).ToString()
FROM    dbo.FailCode
WHERE   Name IN ( '1', '2' );

Children can then be added to each root node as necessary, business as usual stuff from there.

On the adjacency pairs side of your structure, either of the above inserts would do the trick - the ParentID for any root node just has to be NULL. Again, at this point, adding children is pretty evident.

Either way you go about it, the hierarchy capabilities are largely equivalent and either solution can be effective and efficient ( though the adjacency list model will certainly appear to be a little more involved! ).

HIERARCHYID

SELECT  Org = IDOrg.ToString(), ID, Name
FROM    dbo.FailCode
ORDER BY ID;

Adjacency List

;WITH cte_AdjacencyList AS (
    SELECT  Lvl = ROW_NUMBER() OVER (
                ORDER BY ID ), 
            Org = CAST( '/' + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
                ORDER BY ID ) ) + '/' AS VARCHAR( 32 ) ),
            ParentID, ID, Name
    FROM    dbo.FailCode 
    WHERE   ParentID IS NULL
    UNION ALL
    SELECT  Lvl = ROW_NUMBER() OVER (
                ORDER BY fc.ID ),
            Org = CAST ( al.Org + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
                ORDER BY fc.ID ) ) + '/' AS VARCHAR( 32 ) ),
            fc.ParentID, fc.ID, fc.Name
    FROM    dbo.FailCode fc
    INNER JOIN cte_AdjacencyList al
        ON  fc.ParentID = al.ID )
SELECT  Org, ID, Name
FROM    cte_AdjacencyList
ORDER BY ID;