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 On Fri, Jan 29, 2016 at 12:23 PM, Christophe Albrech < [email protected]> wrote: > 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 <[email protected]> wrote: > >> Kevan Stannard > > > > -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/cfaussie. For more options, visit https://groups.google.com/d/optout.
