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.)
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.)