~楓花雪岳~: [SQL] HierarchyID

星期六, 7月 01, 2023

[SQL] HierarchyID - 資料型態

SQL Server 2008 推出 HierarchyID 資料類型，主要是用來呈現 [樹狀圖 (階層式)] 資料，例如組織圖、BOM 表，以往 Table 都是以 [紀錄上一層 (父子關係)] 來記錄，其缺點是搜尋子節點須以 CTE 遞迴擴展子節點，HierarchyID 則是能改善該情況

紀錄上一層

下圖示意圖

左方 Table Schema 透過 ParentEmpID 來記錄上一層資料
右方為透過 CTE 遞迴來呈現整個階層關係

紀錄上一層非該筆記重點，相關應用請參考筆記

HierarchyID

HierarchyID 需以斜線符號 (/) 開頭 (樹狀圖根結點 root)，也必須以斜線符號 (/) 結尾，中間使用數字 (int、decimal) 標示各節點，例如：/1/2.1/3，以 binary 來儲存

下述 Table Script 包含 HierarchyID 欄位和相關應用

CREATE TABLE tblHID  
( 
	ID int PRIMARY KEY CLUSTERED ,
	HID HierarchyID UNIQUE NOT NULL ,
	ParentHID AS HId.GetAncestor(1) PERSISTED REFERENCES tblHID (HID),  
	HLevel AS HID.GetLevel() PERSISTED,   
	EmpID int UNIQUE NOT NULL,  
	EmpName varchar(20) NOT NULL,  
	Title nvarchar(20) NULL 
) ;

Primary Key

Primary Key 預設是 Clustered Index，HierarchyID 是 binary 不適合當成 Clustered Index，所以建立 ID 流水號欄位來當成 Primary Key (Clustered Index)，官方文件範例上介紹 HierarchyID 時都會把它設定為 Primary Key，覺得這是演示方便才這樣直接設定

HierarchyID 本身沒有限定資料是唯一，該點則是透過 unique 來達到、避免重覆，另外 ID 流水號欄位為方便 AP 端事先取得編號來繪製 UI 上的樹狀圖節點，並不會使用 identity，個人是使用 Sequence 來取代 identity，詳見該篇筆記 - [SQL] Sequence

Foreign Key

在 [紀錄上一層] 內紀錄父節點，可以設定 FK 來維持資料一致性，避免孤兒資料產生，該作法也可以應用在 HierarchyID 上，該 FK 觀念可以參考該篇筆記 - [SQL] 自我參考 Foreign Key

該 Table 則是使用計算欄位搭配 HierarchyID.GetAncestor(1) 來取得父節點並設定 FK 來達到需求，但該點不是一定要存在，端看實務上是否有該使用 FK 資料維護需求囉

樹狀圖 Level 欄位

該欄位可以直接使用 HierarchyID.GetLevel 來取得，該欄位關係廣度優先索引建立，也是不一定要存在，參考官方文章 - HierarchyID 的索引策略

HierarchyID 簡易顯示

HierarchyID 為 binary，透過 ToString() 或是 CAST() 可以轉成路徑，較方便閱讀

SELECT
	HID ,
	HID.ToString() AS HIDPath ,
	ParentHID ,
	CAST(ParentHID AS nvarchar(4000)) AS ParentHIDPath,
	HLevel ,
	EmpID ,
	CAST(REPLICATE(SPACE(1) , HLevel * 10) + EmpName as varchar(4000)) AS NameTree
FROM tblHID
ORDER BY HIDPath

參考資料
Hierarchy ID - Model Your Data Hierarchies With SQL Server 2008
階層式資料
ToString、GetAncestor、GetLevel

~楓花雪岳~