Has anyone used the HierarchyID data type in MS SQLServer?

[Mike K]

Is anyone using the HierarchyID data type in MS SqlSERVER to organise data into trees?    

On the face of it,  accepting Microsoft's word for it  (always a risky position to take, I know) it seems like a quick and straightforward way to set up and manipulate data.  

Has anyone any real life experience to draw on to give me an assessment about whether Microsoft is full of it as usual or is it a step forward from using nested sets and a tree table with right nodes and left nodes to manipulate?   (I'm referring to Kevan Stannard's excellent piece on Nested Sets that has kept my data sorted for quite a few years now) 

-- 

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com

[Christophe Albrech]

Nested sets are so rad. I fell in love with them back when I read joe celko's "sql for smarties" and its companion book "tree and hierarchies in sql". Way to think outside the box. It just gets a bit more complex when it comes to moving nodes around, but if you have the sprocs handy, you're in business.

As far as the hierarchy datatype, I've only played with it briefly to study for a cert. It works well, as far as I remember. While under the hood it's pretty much a binary representation of materialized path implementation, the fact that it's a CLR object means it comes with a bunch of helper methods out of the box. Very much like spatial data, if you've ever worked with it (again, awesome).

What I am wondering is why you are considering the change if you are happy with the speed and stability of your current implementation (or are you just curious?). Also consider the fact that using the hierarchyID will make it harder to move to a different DBMS or a pre-2008 server if the need arises. 

To answer your question based on my experimentation and readings, no, MS is not full of it, and yes it's a step in the right direction. Just like CF has always kept adding support for new stuff (xpath, cfdocument...), microsoft is doing the same and that's a good thing. 

On Wed, Jan 27, 2016 at 6:05 PM, Mike K <afpwe...@gmail.com> wrote:

Kevan Stannard

[Mike K]

Thanks for your response, Christophe.     And a good question.    

I've been using Kevan Stannard's way of handling nested sets until now but it is cumbersome in that it has to have 3 fields to position an element in the tree. Moving an element from one place in the tree to another requires quite a lot of sql.   Not a problem if there are only a few movements at a time (e.g. a CMS) but in a products database there could be lots of movements, or mass movements or whole product groups.    

The MS HierarchyID uses a single field, which is part of the element's record along with the part name, part number, etc etc,  and elements can be moved around the tree with a single update statement on a single table, just like updating any other field.     That seems on the face of it to be a much simpler thing to use.  

Hence my question - my understanding is largely theoretical,   from reading and a few small scale experiments.    Before I dive in and commit to using it, I thought I'd see if anyone else had used hierarchyID types and if there were any 'gotcha's'  and if it did in fact deliver the improvements i thought it might. 

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com