Representing parent-child relationship in to tree using Hierarchyid data type

The Hierarchyid data type introduced in SQL Server 2008 is actually a system-supplied common language runtime (CLR) user-defined type (UDT) that can be used for storing and manipulating hierarchical structures (for example, parent-child relationships) in a relational database (*).
Hierarchyid data type is stored as a varbinary value. Hierarchyid help us to query our tree oriented questions such as parent-child relationship or organizational hierarchy without using loops or cursers.
Let us see how we could populate Hierarchyid nodes in action:

To demonstrate this example let us modify our [parent child] to have Hierarchyid column named Node.

ALTER TABLE [Parent Child]
	ADD [Node] hierarchyid;
GO

The simplest way to create the hierarchy node for parent child relationship is by using a CTE recursive queries. If you are not familiar with how to create a recursive query please take a look at my pervious blog post before moving forward by using this link (recursive queries).

;WITH Children AS (
 SELECT  
      P.ID
     ,P.ParentID
     ,P.Name
     ,CAST(ROW_NUMBER() 
           OVER (PARTITION BY ParentID ORDER BY ID) AS VARCHAR) + '/' AS Child
 FROM dbo.[Parent Child] AS P	  
 WHERE P.ID !=P.ParentID
) 
, NoChildren AS (
-- Anchor member    --
 SELECT  0 AS [Level]
        ,P.ID
        ,P.ParentID
        ,P.Name
        , hierarchyid::GetRoot() AS Node 			
 FROM dbo.[Parent Child] AS P	  
 WHERE  P.ParentID IS NULL --P.ID =P.ParentID
 
UNION ALL
-- Recursive member --
 SELECT  NC.[Level] + 1 AS [Level]
         ,C.ID
         ,C.ParentID
         ,C.Name			
         , CAST(NC.Node.ToString() + C.Child AS hierarchyid)  AS Node
 FROM dbo.[Parent Child] AS P	  
 JOIN NoChildren NC ON P.ParentID = NC.ID
 JOIN Children C ON P.ID = C.ID
)
 
UPDATE P SET P.Node = NC.Node,P.[Level] = NC.[Level]
FROM [Parent Child] P
	JOIN NoChildren NC ON P.ID = NC.ID;
GO

If we query the our table we will get the following result set

SELECT   ID
        ,ParentID
        ,Name		
        ,[Level]
        ,Node
        ,Node.ToString() [String VALUE] 
--ToString() function gives us the string value of the node	
FROM [Parent Child] 
ORDER BY Node;
GO

Read More:
(*) Microsoft® SQL Server 2008 R2 UNLEASHED by (Rankins, Bertucci, Gallelli, Silverstein)
http://technet.microsoft.com/en-us/library/bb677290.aspx