HierarchyID

Posted on January 20, 2011

0



Introduced in SQL Server 2008, the HierarchyID is a CLR that uses a binary form of the enumerated path method described in a previous post on this blog. Using prebuilt CLR functions it is relatively simple to write queries to retrieve descendants and ancestors.

So let’s look at retrieving the descendants of Baby Food as in our previous examples. First we build our source product table, and then be build our ProductHID table, containing the HierarchyID data type.

IF OBJECT_ID('dbo.Product') IS NOT NULL DROP TABLE dbo.Product
GO
CREATE TABLE dbo.Product(ProductID int not null PRIMARY KEY
, ProductName varchar(100)
, ParentProductID INT)
GO
INSERT INTO dbo.Product(ProductID, ProductName, ParentProductID)
VALUES (0,'Baby Goods',NULL)
, (10,'Baby Food',0)
, (20,'Nappies',0)
, (100,'All Ages Baby Food',10)
, (150,'Beginners',10)
, (200,'Strawberry Yoghurt',100)
, (250,'Baby Cereal',100)
, (300,'Formula Milk',150)
, (310,'Heinz Formula',300)
, (400,'Small Pack Nappies',20)
, (450,'Bulk Pack Nappies',20)
GO
IF OBJECT_ID('dbo.ProductHID') IS NOT NULL DROP TABLE ProductHID
GO
CREATE TABLE dbo.ProductHID(
ProductID int not null,
ProductName VARCHAR(100),
hid     HIERARCHYID NOT NULL,
lvl AS hid.GetLevel() PERSISTED)
GO

Note above that we use a function to retrieve the hierarchy level and persist it in the table.

;WITH CTE AS
( 
SELECT  p.ProductName
  , p.ProductID
  , '/' + CAST(p.ProductID AS VARCHAR(MAX)) + '/'  AS TreePath
  , ParentProductID  
 FROM dbo.Product p  
 WHERE p.ParentProductID IS NULL
 UNION ALL
 SELECT p.ProductName
  , p.ProductID
  , CAST(H.TreePath
+ CAST(p.ProductID AS VARCHAR(10)) + '/' AS VARCHAR(MAX)) AS TreePath
  , p.ParentProductID  
 FROM dbo.Product p  
 INNER JOIN CTE H
 ON H.ProductID=p.ParentProductID  
)  
INSERT INTO dbo.ProductHID
SELECT ProductID
, ProductName
,CAST(Treepath AS HIERARCHYID) AS hid
FROM cte
GO

Note above that we cast our enumerated treepath as a HierarchyID data type.

To show the results of the hierarchyID we use a function ToString:

SELECT REPLICATE('   ', lvl) + ProductName AS ProductName
 , hid.ToString() AS TreePath
FROM dbo.ProductHID
ORDER BY hid;

This returns:

 To get the descendants, we do a self-join and use the IsDescendantOf function:

SELECT REPLICATE('   ', c.lvl) + c.ProductName AS ProductName
FROM dbo.ProductHID c
JOIN dbo.ProductHID p
ON p.ProductName = 'Baby Food'
AND c.hid.IsDescendantOf(p.hid) = 1
ORDER BY c.HID

returning:

hierarchyID descendants

 

 To get the parent of Heinz Formula:

SELECT p.ProductName AS ProductName
FROM dbo.ProductHID c
JOIN dbo.ProductHID p
ON c.hid.GetAncestor(1) = p.hid
WHERE c.ProductName = 'Heinz Formula'

Returns:

Formula Milk

To get the ancestors of Heinz Formula:

SELECT p.ProductName AS ProductName
FROM dbo.ProductHID p
JOIN dbo.ProductHID c
ON c.hid.IsDescendantOf(p.hid) = 1
WHERE c.ProductName = 'Heinz Formula' 
 
To join the results to a fact table to get the aggregates is very straight forward:
SELECT p.ProductID
, p.ProductName
, SUM(SaleQty) AS SaleQty
FROM dbo.ProductHID c
JOIN dbo.ProductHID p
ON c.hid.IsDescendantOf(p.hid) = 1
INNER JOIN dbo.ProductSale s
ON s.ProductID = c.ProductID
GROUP BY p.ProductID,p.ProductName
ORDER BY p.ProductID

This gives up:

Advertisements
Posted in: Data Modeling, SQL