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 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)).
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:
Node.GetPreceding(n)
- gets the nth preceding node of the current node (XPath: preceding).
Node.GetFollowing(n)
- gets the nth following node of the current node (XPath: following).
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 position n 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: > 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. Books Online says: Returns true for all the nodes in the sub-tree rooted at parent, and false for all other nodes. And that does indeed include parent itself, although it could be spelled out more clearly. But I agree that it is confusing, given the name of the method. I guess that the rationale is that when you say: SELECT ... FROM tbl WHERE @.node.IsDescendant() = 1 You want all nodes below @.node including @.node itself. -- 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|||(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:
IsDescendant(@.node)
- considers children (and childrens grand children).
IsDescendantOrSelf(@.node)
- considers children (and childrens grand children) including the current node.
|||(Anders Borum@.discussions.microsoft.com) writes: > 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. Yes, this is not the place for hot action. > Where should I post these suggestions if not on this forum?! https://connect.microsoft.com/SQLServer/Feedback is the place where you should file bugs and suggestions for SQL Server. -- 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|||
Thanks for the Url on bugs / suggestions. Hopefully, questions and suggestions are more appreciated there. You should btw. think about the formatting of your messages. It's close to 100% impossible to separate your own words from the quotes.
For those interested, I've started a thread at the SQL bugs / suggestion site (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=297366).
Thanks for participating in the thread!
