When you ask for a new HierarchyID between two others, the result gets progressively longer. For example, between 2/5.6 and 2/5.7 there's only 2/5.6.1 and other 4 component paths. The HierarchyID data type is limited to 800 some bytes, so you can't repeat this forever. Then again, integer types are also limited, but it isn't a problem in practice. Should I periodically defragment my table so that height doesn't grow unbounded?
It's considered a "best practice" with the hierarchyid
to "append" new IDs so that you don't use those in-between states (such as /2/5.6/
) at all. If your hierarchyid
is a clustered primary key then that's bad for performance, it will cause page splits similar to the way a uniqueidentifier
will.
If you generate sequential children, it's highly unlikely that you'd ever need to worry about running out; you can have literally millions of children for each parent.
Here is an example of how you're expected to generate hierarchyid
values:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
UPDATE Hierarchy
SET @LastChild = LastChild = HId.GetDescendant(LastChild, NULL)
WHERE HId = @ParentID
INSERT Hierarchy (HId, ...)
VALUES (@LastChild, ...)
COMMIT
If you generate the ids this way, rest assured you'll never have to worry about running out.
For curiosity's sake, I ran a quick test to find out for sure how deep you can go. Here's a test script:
DECLARE
@parent hierarchyid,
@child hierarchyid,
@high hierarchyid,
@cnt int
SET @parent = '/1/'
SET @child = @parent.GetDescendant(NULL, NULL)
SET @cnt = 0
WHILE (@@ERROR = 0)
BEGIN
SET @cnt = @cnt + 1
PRINT CAST(@cnt AS varchar(10)) + ': ' + @child.ToString()
SET @high = @parent.GetDescendant(@child, @high)
SET @child = @parent.GetDescendant(@child, @high)
END
You can see it error out at a point-nesting level of 1426, so that's your worst-case limit for how many "in-between" nodes you can create, worst case meaning that every single insertion goes in between the two most deeply-nested nodes.
As I mentioned in the comments, it's pretty hard to hit this limit, but that still doesn't make it a good idea to try. The actual byte length gets longer as longer as you use up more and more "points", which degrades performance. If the hierarchyid
is your clustered index, this will kill performance by page splits. If you're trying to rank nodes by parent then use a ranking column instead; it's easier and more efficient to sort from a later SELECT
than it is to do during your INSERT
where you have to worry about transaction isolation and other such headaches.