H is for… HierarchyID

re: H is for… HierarchyID

Kevin Russell — Mon, 23 Jul 2012 17:13:04 GMT

Why would this ever be needed? Entity Framework does not support it, LINQ2SQL does not support it...I fail to understand why all of these unsupported bells and whistles keep appearing when they are pretty much useless to anyone developing a business application.

re: H is for… HierarchyID

Jim O'Neil — Thu, 04 Nov 2010 20:05:51 GMT

For the post, I 'cheated'. I used PowerPoint SmartArt (one of the hierarchy formats) to generate the pictures from the values I entered manually. That said, PowerPoint does have object model you can programmatically access via VSTO (Visual Studio Tools for Office), so there may be a way to generate them.

re: H is for… HierarchyID

Net Boy — Thu, 04 Nov 2010 00:41:26 GMT

Great article!

Is there a way to generate the nice pictures from your data?

re: H is for… HierarchyID

Jim O'Neil — Fri, 09 Jul 2010 18:24:52 GMT

Sam, basically select using IsDescendant and see if the count is > 1 (a node is considered to be a descendant of itself). For example, ken0 is the CEO and ben0 is a buyer (leaf node):

// this returns 290 (not a leaf)

SELECT COUNT(*) FROM HumanResources.Employee

WHERE OrganizationNode.IsDescendantOf((SELECT OrganizationNode FROM HumanResources.Employee

WHERE LoginID = 'adventure-works\ken0')) = 1

// this returns 1 (leaf node)

SELECT COUNT(*) FROM HumanResources.Employee

WHERE OrganizationNode.IsDescendantOf((SELECT OrganizationNode FROM HumanResources.Employee

WHERE LoginID = 'adventure-works\ben0')) = 1

HTH, Jim

re: H is for… HierarchyID

Sam High — Thu, 08 Jul 2010 16:10:27 GMT

How can I tell if a hierarchyid has any descendants (Am I a Leaf or a Node)

re: H is for… HierarchyID

Roger Mathews — Sat, 27 Mar 2010 20:49:52 GMT

This is a great article. I am very excited with this new feature of SQL as it has tremendous potential, especially in our environment.

Cheers!

re: H is for… HierarchyID

Kevin — Mon, 26 Oct 2009 16:57:39 GMT

'GetAncestor' ...

See the method reference... it's all case sensitive as the hierarchyid is effectively a CLR: http://technet.microsoft.com/en-us/library/bb677193.aspx

re: H is for… HierarchyID

Leon Han — Wed, 07 Oct 2009 07:18:05 GMT

Thanks for the answer. I am surprised the Function is case sensitive!

The error I got is:

Msg 6506, Level 16, State 10, Line 15

Could not find method 'getAncestor' for type 'Microsoft.SqlServer.Types.SqlHierarchyId' in assembly 'Microsoft.SqlServer.Types'

Just put it here so anyone who may see this error can get the answer from here. :)

re: H is for… HierarchyID

Jim O'Neil — Fri, 17 Jul 2009 19:00:15 GMT

TBone, it's case-sensitive. Try select hid.GetLevel() from x.

re: H is for… HierarchyID

TBone — Fri, 17 Jul 2009 18:49:44 GMT

Cant get it to work - Real simple example:

create table x (hid hierarchyid)

select hid.getlevel() from x

Msg 6506, Level 16, State 10, Line 1

Could not find method 'getlevel' for type 'Microsoft.SqlServer.Types.SqlHierarchyId' in assembly 'Microsoft.SqlServer.Types'