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: > 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:
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?!
(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!
Hi, Anders
I'm very glad to see that you like the new HierarchyID type which we're introducing in SQL Server 2008 and are willing to take the advantage of it for your existing application.
I also appreciate the great feedbacks and suggestions you're giving, and i'm sure we'll take serious consideration about them and try our best to esnure that we're providing satisfied functionalities.
I tend to agree with your suggestion on replacing the naming of GetDescendant with GetChild as it's more clear and aligned with the industry in terms of hierarchy support.
Given the time and resource concern we have had for the new release, our goal is to make sure the core and base functionalities being provided at first before other advanced features. However, regarding your suggestion to add additional methods like GetPreceding and GetFollowing, we would definitely like to take some serious triage/consideration and see we can fit them into the release along with other requests. Meanwhile, as a workaround, you could use given GetAncestor, IsDescendant and regular comparison operators (i.e. >, < and etc) by writing your own function or procedure to resolve it.
thanks-michael
Again, very good feedback and suggestion about IsDescendant.
To return true for a node to call IsDescendant by itself is actually by design as we consider it seems more comfortable for regular subtree queries. We'll revisit our design by seriously considering your suggestion and ensure we're providing the most valuable solution.
Meanwhile, we're also considering to replace the name from IsDescendant with IsAncestor as the method actually means the caller node is a parent of the argument node and the current name is misleading by against the common naming rule.
thanks-michael
Hi, Anders
Sorry for getting back to you late on this.
Please feel free to send me direct emails regarding HierarchyID in the future, my MS alias is 'mfwang'.
As i said in couple of replies i sent, i hear and appreciate your feedbacks and suggestions, and we'll definitely consider them seriously.
thanks-michael
Can i put in two cents' worth? 
BOL:
Hierarchical relationships represented by hierarchyid values are not enforced like a foreign key relationship. It is possible and sometimes appropriate to have a hierarchical relationship where A has a child B, and then A is deleted leaving B with a relationship to a nonexistent record. If this behavior is unacceptable, the application must query for descendants before deleting parents.
It would be nice to enforce "integrity" somehow when necessary, is it possible?
p.s. excuse my English
LR= at 2007-10-3 >
