New SQL Server Data Type: HierarchyId

Posted by Jonathan Allen on Nov 29, 2007

Sections

Architecture & Design,

Development,

Operations & Infrastructure

Topics

.NET ,

Data Access

Tags

SQL Server 2008

Tree-like hierarchies has always a problem in relational databases. Microsoft's first attempt at addressing this was in SQL Server 2005 with the implementation of Common Table Expressions.

While CTEs work over existing schema, Microsoft has sought a way to treat hierarchies as first-class concepts. To this effect, they have introduced the HierarchId data type in SQL Server 2008.

In traditional hierarchies, a row merely stores a reference to its parent node. This allows a row to know its relative position in the hierarch, but not its absolute position. Changing the parent is an atomic update that does not affect any child rows.

A HierarchyId field stores the rows exact position in the row in the hierarchy. An example provided by Denny Cherry shows values of 0x, 0x58, and 0x5AC0. These have string representations of /, /1/, and /1/1/ respectively. This leads to questions about consistency and performance, especially when changing the parent of a row. Ravi S.Maniam recommends that databases be designed such that reparenting occurs infrequently.

The HierarchyId field comes with a whole host of functions. GetAncestor and GetDescendant can be used to walk the tree. ToString and Parse methods are used for converting between the binary and string representations of the field. Strangely, there are also methods for supporting BinaryReaders and BinaryWriters.

Going back to the GetDescendant, it is a rather curious method. It does not actually return child rows, but rather it returns locations of potential child nodes. To insert a new row into the tree, it must be called the get the slot of the last child for the given parent, then a second time to get an empty slot just after that slot.

So far there aren't any good examples of actually retrieving a tree using T-SQL. In fact, everything about it has a imperative rather than set-based feel to it.

1. Back to top

   Data Type vs special functionality

   by Alex Popescu

   I am not a DB guy, and I have to confess that this sounds pretty weird. I can see how it addresses part of the problem, but I cannot imagine very good solutions for solving the reparenting problem (well, I may be imagining a couple, but as I said I am not into DBs too much). Personally, I find Oracle approach (introducing new SQL constructs to deal with hierarchies) a more scalable approach.
   
   ./alex
   --
   .w( the_mindstorm )p.
   Alexandru Popescu
   Senior Software Eng.
   InfoQ Techlead/Co-founder

   Reply

• Older >