Questions (and suggestions) on HierarchyID datatype
Hello!
I have a few questions (and suggestions) related to the new HierarchyID datatype in SQL Server 2008 (Katmai).
In general, I'm really pleased to see a native hierarchical datatype. Having tested various tree operations using the new datatype over the weekend, it looks like a sound replacement for our adjacency lists and nested set models (tree structures in relational databases).
I believe many developers automatically try to apply their knowledge of XPath when working with the HierarchyID datatype. As these developers probably have been working with XML (i.e. XmlDocument, XPath or DOMs in general), it would make for an extremely easy transition to the T-SQL equivalent if the behaviour (and API) of HierarchyID was aligned with existing APIs.
Parent.GetDescendant(Child1, Child2):
According to the documentation, this method provides a deterministic way to generate a new position between the children of the parent node (and optionally preceding / following argument Child 1 / Child 2).
The nameGetDescendant is misleading as it indicates that the method inspects all descending nodes (children, grandchildren, etc.) of the current node (XPath: descendant). As the method only inspects the children of the current node (XPath: child), the name should be changed toGetChild (i.e. Parent.GetChild(Child1, Child2)).
Suggestion for additional methods on HierarchyID:
When working with hierarchical data, you have to realize there are other axis than up (XPath: ancestor) or down (XPath: child, descendant). It would extremely helpful, if the HierarchyID datatype provided additional methods for inspecting preceding / following siblings.
Therefore, with the current API in mind I suggest implementing two additional methods:
To align with the current API, the rules that govern the GetDescendant(Child1, Child2) method should apply to these methods (i.e. a call to Node.GetPreceding(n) yields NULL if the node at positionn is NULL and so forth).
If missing from the API, developers would have to implement these methods as custom T-SQL functions that leverage the MIN / MAX approach (in combination with a TOP statement) to find preceding(n) / following(n) sibling.
Please let me know if I'm posting this in the wrong forum. The developers I've talked to think they're perfectly valid suggestions that would improve the use of the new datatype and ease the transition.
Hello!
Just adding an additional question to the list (might be caused by beta (unfinished) documenation or the fact that the HierarchyID datatype was left incomplete as of the June CTP). I'm implementing basic tree operations (like moving subsets etc.) and ran acros some strange results because of unexpected behaviour working the methods exposed from the datatype.
node.IsDescendant(node):
When node.IsDescendant() method is called with "this" as the argument (i.e. @child.IsDescendant(@child)), the method returns true. This is not the expected behaviour, as a node never can be a child of itself.
Either change the name to IsDescendantOrSelf (again referencing the corresponding XPath axis that match the current behaviour and known APIs) or change the behaviour to return false when calling with "this" as the argument (i.e. @child.IsDescendant(@child)).
I think the HierarchyID is extremely useful, but it's quite hard to predict the behaviour from the current method names.
There's probably a ton of good reasons why the current behaviour is as it is, but I'd really like to know why. And I'd also like to know if my findings are valid and what the SQL Server Engine Team team would do about it.
(Anders Borum@discussions.microsoft.com) writes: > Parent.GetDescendant(Child1, Child2): > > According to the documentation, this method provides a deterministic way > to generate a new position between the children of the parent node (and > optionally preceding / following argument Child 1 / Child 2). > > > > The name GetDescendant is misleading as it indicates that the method > inspects all descending nodes (children, grandchildren, etc.) of the > current node (XPath: descendant). As the method only inspects the > children of the current node (XPath: child), the name should be changed > to GetChild (i.e. Parent.GetChild(Child1, Child2)). It tripped me as well. But rather because the method does not retrieve any existing descendant, but a new one. So GetNewDescendant ot GetNewChild would be clearer. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Hello!
I just realized that as the API exposed by the HierarchyID doesn't consider existing data in the table (i.e. it's data agnostic), the use of Preceding / Following may be limited to simply extent the functionality of the existing GetDescendant() method. With that in mind, most developers would probably just stick with GetDescendant.
However, the name of the GetDescendant() method is still misleading. I agree with "MVP User" to change the name. In addition, the IsDescendant() method should be broken in two different methods:
Or
Are there no developers from the SQL Server Engine Team reading this forum? Compared to the other foras around here, these threads are left completely dead. Comments are appreciated.
Where should I post these suggestions if not on this forum?!
