0

With the hierarchyid datatype in SQL Server 2008 and onward, would there be any benefit to trying to optimize the issuing of the next child of /1/1/8/ [ /1/1/8/x/ ] such that x is the closest non-negative whole number to 1 possible?

An easy solution seems to be to find the maximum assigned child value and getting the sibling to the right but it seems like you'd eventually exhaust this (in theory if not in practice) since you're never reclaiming any of the values and to my understanding, negatives and non-wholes consume more space.

EXAMPLE: If I've got a parent /1/1/8/ who has these children (and order of the children doesn't matter and reassignment of the values is ok):

/1/1/8/-400/
/1/1/8/1/
/1/1/8/4/
/1/1/8/40/
/1/1/8/18/
/1/1/8/9999999999/

wouldn't I want the next child to have /1/1/8/2/ ?

TechDo
  • 18,398
  • 3
  • 51
  • 64
Hans Reiser
  • 1
  • 1

1 Answers1

0

Here's the thing.

What you are saying will be "optimal" is not necessarily optimal.

When I am inserting values into a hierarchy, I generally do not care what the order is for the child nodes of a particular node.

If I do, that is why there are two parameters in GetDescendant.

If I want to prepend the node into the order(i.e make it first), I use a first parameter of NULL and a second parameter that is the lowest value of the other children.

If I want to append the node into the order (i.e. make it last), I use a first parameter of the maximum value of the other children and a second parameter of NULL.

If I want to insert between two other child nodes, I need both the one that will be before and the one that will be after the node I am inserting.

In any case, generally the values in the hierarchy field don't really matter, because you will order by a different field like Name or something.

Ergo, the most "efficient" method of adding things into a hierarchy is to either prepend or append, since finding the MIN or MAX hierarchy value is easy, and doing what you are describing requires several queries to find the first "hole" in the tree.

In other words, don't put a lot of meaning onto the string representation of a hierarchy unless you are using them for an application in which you are using the hierarchy value to sort by.

Even in that case, you probably don't want to fill in hierarchy values as you describe, and probably want to append to the end anyway.

Hope this helped.

PlayDeezGames
  • 752
  • 4
  • 12