sql-serverhierarchyid

SQL 2008 HierarchyID with Multiple Root Nodes


I wanted to use the new HierarchyID type in SQL Server 2008 to handle the page relations in a small wiki application. However It would need to have multiple root nodes since every main article/page per account would be a root node.

From what I have read the HierarchyID type only allows 1 root node per column is this correct? and is there any way to enable multiple root nodes ?


Solution

  • Yes, you are reading right - using the HierarchyID allows only one single root node. That's the way it is and there's no way around it, as far as I know, short of introducing an artificial new "über-root" which serves no other purpose than to allow you to have several first-level "sub-root"....

    Marc

    Update: as Greg (@Greg0) has pointed out - this answer is actually not correct - see his answer for more details.