New SQL Server Data Type: HierarchyId

| by Jonathan Allen Follow 576 Followers on Nov 29, 2007. Estimated reading time: 1 minute |

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.

Rate this Article

Adoption Stage

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

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.

.w( the_mindstorm )p.
Alexandru Popescu
Senior Software Eng.
InfoQ Techlead/Co-founder

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

1 Discuss

Login to InfoQ to interact with what matters most to you.

Recover your password...


Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.


More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.


Stay up-to-date

Set up your notifications and don't miss out on content that matters to you