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