Quering hierarchyid nodes in SQL Server

In this blog post we will see some of the things that we could easily query our tree oriented data using hierarchyid. We can achieve that using isDecendanteOf() and getLevel() functions.

Let us see how we could do that in action.
We will be using this table that has a hierarchyid column.

SELECT   ID
	,ParentID
	, Name		
	,[Level]
	,Node
	,Node.ToString() [String VALUE]	
FROM [Parent Child] 
ORDER BY Node;
GO

1. One of the question we could easily answer is “who are the direct descendants/ children of the Noah?”. This can be done by making isDencendentOf(Noah) equals to 1. Since isDencendatOf(Noah) includes children of Noah and Noah(self) we could exclude Noah by Node.getLevel() = Noah.getLevel()+1. This pseudo code can rewritten in SQL as follows

--direct decendant / next generation of Noah?--
DECLARE @PARENT AS hierarchyid;
SELECT @PARENT = Node FROM [Parent Child] WHERE Name ='Noah';
 
SELECT   ID
	,ParentID
	,Name		
	,[Level]
	,Node
	,Node.ToString() [String VALUE]	
FROM [Parent Child] 
WHERE Node.IsDescendantOf(@PARENT) = 1 AND
	  Node.GetLevel() = @PARENT.GetLevel()+1
ORDER BY Node;
GO

Quering hierarchyid - Step2
2. The other question we could as is the total number of descendants of a given node in a tree.

-- Count total number of descendant--
SELECT   Name		
	,(SELECT COUNT(*)-1 -- minus 1 is because IsDescenantOf includes self
          FROM [Parent Child] C
          WHERE C.Node.IsDescendantOf(P.Node) = 1 
             -- C.Node.GetLevel() = P.Node.GetLevel()+1
) [Total generation]	
FROM [Parent Child] P
ORDER BY [Total generation] DESC;
GO

Quering hierarchyid - Step3
3. We could as also ask to get all the leaf nodes or people with no descendant in our table

--Pleople with no descendant--
SELECT   C.ID
	,C.ParentID
	,C.Name		
	,C.[Level]
	,C.Node
	,C.Node.ToString() [String VALUE]	
FROM [Parent Child]  P
JOIN [Parent Child]  C ON 
	C.Node.IsDescendantOf(P.Node) =1 AND
	C.Node.GetLevel() = P.Node.GetLevel()+1	AND 
	C.Node.GetDescendant(NULL,NULL) 
		NOT IN (SELECT Node FROM [Parent Child])
ORDER BY Node;
GO

Quering hierarchyid - Step4