I recently grabbed the new July CTP (CTP4) for SQL 2008 - Katmai.

I'm still looking through things, for instance Notification Services has been dropped, and Reporting Services has undergone some serious surgery.

One thing that did intrigue me though was a new data type for supporting hierarchical data: hierarchyid.

This looks really, really cool. Basically it lets you store tree structured data in SQL and includes methods to make working with that data really simple. You get methods like IsDescendant, GetAncestor, GetLevel, ReParent (for moving nodes/subtrees) and so forth.

You can index either breadth-first or depth-first, so you can index based on how you typically traverse your tree.

Here’s an example table with breadth first indexing:

CREATE TABLE Organization (
  EmployeeID hierarchyid,
  OrgLevel as EmployeeID.GetLevel(),
  EmployeeName nvarchar(50) NOT NULL 
); 
CREATE CLUSTERED INDEX Org_Breadth_First
ON Organization(OrgLevel,EmployeeID) ;

The GetLevel() on the OrgLevel column is so you know what depth a record (node) is at in the tree.

Use the GetRoot() function to get the root of a tree (note the double colon syntax). For example, this inserts a new root node and then selects it. 

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES (hierarchyid::GetRoot(), 1, 'Me', 'CEO') ;
SELECT * FROM HumanResources.EmployeeOrg WHERE OrgNode = hierarchyid::GetRoot() ;

The other interesting thing is that hierarchyid’s have a ToString() function that spits out hierarchy paths similar to the following examples:


  • /

  • /1/

  • /0.3.-7/

  • /1/3/

  • /0.1/0.2/

Nodes can be inserted in any location. Nodes inserted after /1/2/ but before /1/3/ can be represented as /1/2.5/. Nodes inserted before 0 have the logical representation as a negative number. For example, a node that comes before /1/1/ can be represented as /1/-1/.

It’s a little weird to look at but it makes sense.

Performance is optimised for selecting data, so anything that does large amounts of inserts/deletes or reparenting (think sales order header/order lines for example) may not be a good candidate for hierarchical data.