樹形層次結構（Hierarchy）經常出現在有結構的資料中，T-SQL新增資料型別HierarchyID， 其長度可變，用於儲存層次結構中的路徑。HierarchyID表示的層次結構是樹形的，由應用程式來生成和分配 HierarchyID的值，建立父子節點之間的關係。
HierarchyID資料型別支援深度優先順序的比較，對於兩個HierarchyID值 a和b，a<b意味著，在深度優先遍歷時，先遍歷到a，後遍歷到b，也就是說，值越小，越接近根節點。對Hierarchy資料型別建立索引，是按照深度優先，先左後右的順序來排序的。左和右是根據節點的值來判斷的，在同一深度上，值較小的節點在父節點的左邊。
一，型別的賦值
HierarchyID資料型別儲存的是單個節點在樹形結構中的路徑（Path），路徑從根節點（Root Node）開始，根節點是“/”，路徑以“/”結尾，使用整數表示一個節點。這意味著HierarchyID的值必須以“/”開頭，以“/”結尾，“/”之間使用數值（正整數或正小數）標識一個元素，例如：“/”，“/1/2/”，“/1/2/3/”，"/1/2.1/3"。
有3種賦值方式，通過字串賦值，字串轉換和通過整數賦值。
declare @ha HierarchyID declare @hb HierarchyID declare @hc HierarchyID set @ha='/1/2/3/' set @hb=HierarchyID::Parse('/1/2/3/') set @hc=0x5B5E select @ha as ha,@hb.ToString() as hb,@hc.ToString() as hc
二，按深度優先順序進行比較
給定兩個 hierarchyid 值 a 和 b，a<b 表示在對樹進行深度優先遍歷時，先找到 a，後找到 b。hierarchyid 資料型別的索引按深度優先順序排序，在深度優先遍歷中相鄰的節點的儲存位置也相鄰。同級別的節點，左邊節點小於右邊節點，表示左邊先被遍歷到。
declare @ha HierarchyID declare @hb HierarchyID declare @hc HierarchyID set @ha=HierarchyID::Parse('/1/2/') set @hb=HierarchyID::Parse('/1/2/3/') set @hc=HierarchyID::Parse('/1/2/4/') select iif(@ha>=@hb,'>=','<'),iif(@hb>=@hc,'>=','<')
三，用於HierarchyID資料型別的函式
1，獲取當前值的級數（Level）
呼叫GetLevel()檢視HierarchyID的Level，值是從root節點開始的層數
declare @ha HierarchyID set @ha=HierarchyID::Parse('/1/2/3/') select @ha.GetLevel() as Level
2，獲取根節點
靜態方法GetRoot()，靜態方法的呼叫格式：HierarchyID::GetRoot()
select HierarchyID::GetRoot().ToString() as TootString,HierarchyID::GetRoot() as RootHierarchyID
3，返回子節點
GetDescendant(childleft,childright)用以返回父級的一個子節點，返回的子節點和child是同level的。
declare @sa Nvarchar(100) declare @sb Nvarchar(100) declare @sr Nvarchar(100) declare @ha HierarchyID declare @hb HierarchyID declare @hr HierarchyID set @sa='/1/2/3/' set @sb='/1/2/6/' set @sr='/1/2/' set @ha=HierarchyID::Parse(@sa) set @hb=HierarchyID::Parse(@sb) set @hr=HierarchyID::Parse(@sr) select @hr.GetDescendant(null,null).ToString(), @hr.GetDescendant(@ha,null).ToString(), @hr.GetDescendant(@ha,@hb).ToString()
如果LeftChild是‘/1/2/3’，RightChild是‘/1/2/4’，需要在這兩個節點之間插入一個新的節點，需要如何處理?表示節點的數字，並不一定必須是正整數，小數也可以，如下，NewChild=’/1/2/3.1/‘；
declare @sa Nvarchar(100) declare @sb Nvarchar(100) declare @sr Nvarchar(100) declare @ha HierarchyID declare @hb HierarchyID declare @hr HierarchyID set @sa='/1/2/3/' set @sb='/1/2/4/' set @sr='/1/2/' set @ha=HierarchyID::Parse(@sa) set @hb=HierarchyID::Parse(@sb) set @hr=HierarchyID::Parse(@sr) select @hr.GetDescendant(null,null).ToString(), @hr.GetDescendant(@ha,null).ToString(), @hr.GetDescendant(@ha,@hb).ToString()
4，判斷兩個節點之間的父子關係
判斷是否是節點的後代，child.IsDescendantOf(parent)，如果是，返回1，如果不是，返回0
declare @sa Nvarchar(100) declare @sb Nvarchar(100) declare @sr Nvarchar(100) declare @ha HierarchyID declare @hb HierarchyID declare @hr HierarchyID set @sa='/1/2/3/' set @sb='/1/2/6/' set @sr='/1/2/' set @ha=HierarchyID::Parse(@sa) set @hb=HierarchyID::Parse(@sb) set @hr=HierarchyID::Parse(@sr) select @ha.IsDescendantOf(@hr), @hb.IsDescendantOf(@hr), @ha.IsDescendantOf(@hb)
四，HierarchyID的值的更新
更新HierarchyID的值，必須級聯地更新與該節點相關的子節點的值，這是由於HierarchyID型別自身的侷限性導致的。
HierarchyID資料型別具有以下侷限性：
- 型別為 HierarchyID的列不會自動錶示樹。由應用程式來生成和分配 hierarchyid 值，使行與行之間的所需關係反映在這些值中。 某些應用程式可能具有 hierarchyid 型別的列，該列指示在另一個表中定義的層次結構中的位置。
- 由應用程式來管理生成和分配 hierarchyid 值時的併發情況。不能保證列中的 hierarchyid 值是唯一的，除非應用程式使用唯一鍵約束或應用程式自身通過自己的邏輯來強制實現唯一性。
- 由 hierarchyid 值表示的層次結構關係不是像外來鍵關係那樣強制實現的。 可能會出現下面這種層次結構關係而且有時這種關係是合理的：A 具有子級 B，然後刪除了 A，導致 B 與一條不存在的記錄之間存在關係。 如果這種行為不可接受，應用程式在刪除父級之前必須先查詢其是否有後代
1，建立資料來源
create table dbo.emph2 ( idpath hierarchyid not null primary key, id int not null, parentid as idpath.GetAncestor(1) persisted foreign key references dbo.emph2(idpath), descr varchar(100) )
idpath=’/1/2/6/‘的子孫節點如下圖
select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr from dbo.emph2 e where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1
2，把子節點變成另一個節點的父節點
例如，把idpath=’/1/2/6/‘ 的節點刪除，並將其子節點的父節點變更為idpath=’/1/2/7/‘
由於存在外來鍵關係，必須先變更子節點的父節點，然後再刪除idpath=’/1/2/6/‘ 的節點。
--delete child notes --select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr update e set e.idpath=HierarchyID::Parse('/1/2/7/'+cast(e.id as varchar)+'/') from dbo.emph2 e where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1 and e.idpath!=HierarchyID::Parse('/1/2/6/') --delete parent note delete dbo.emph2 where idpath=HierarchyID::Parse('/1/2/6/') --check select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr from dbo.emph2 e where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/7/'))=1
3，變更父節點
例如，把idpath=’/1/2/6/‘的節點的父節點變更，其子節點仍然是其子節點。
思路是新建一個節點，並將子節點都掛在新節點下。
--create new node insert into dbo.emph2(idpath,id,descr) select HierarchyID::Parse('/1/3/6/'),id,descr from dbo.emph2 e where e.idpath=HierarchyID::Parse('/1/2/6/') --delete child notes --select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr update e set e.idpath=HierarchyID::Parse('/1/3/6/'+cast(e.id as varchar)+'/') from dbo.emph2 e where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1 and e.idpath!=HierarchyID::Parse('/1/2/6/') --delete parent note delete dbo.emph2 where idpath=HierarchyID::Parse('/1/2/6/') --check select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr from dbo.emph2 e where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/3/6/'))=1
4，定向插入新的節點
由於節點之間存在先後順序，使用GetDescendant(ChildLeft,ChildRight)保證順序。
在節點 idpath=’/1/2/6/‘ 的子節點 id=15，id=16之間插入一個新的子節點，新的子節點的id=36，descr=‘E1136’，思路是使用GetDescendant(ChildLeft,ChildRight)獲取新的IDPath，然後插入到表中。
declare @id int declare @descr Nvarchar(100) declare @sa Nvarchar(100) declare @sb Nvarchar(100) declare @sr Nvarchar(100) declare @hnew HierarchyID set @id=36 set @descr='E1136' set @sa='/1/2/6/15/' set @sb='/1/2/6/16/' set @sr='/1/2/6/' set @hnew= HierarchyID::Parse(@sr).GetDescendant(HierarchyID::Parse(@sa),HierarchyID::Parse(@sb)) insert into dbo.emph2(idpath,id,descr) values(@hnew,@id,@descr)
select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr from dbo.emph2 e where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1 order by e.idpath
從排序的結果集中可以看出，id=36的節點，處於id=15和id=16的節點之間，通過GetDescendant(ChildLeft,ChildRight)實現了順序。
五， 遍歷
HierarchyID型別的資料，很容易實現廣度優先遍歷和深度優先遍歷
1，廣度優先遍歷是指查詢層次結構中相同級別的節點
select idpath.ToString() as IDPath,id,parentid.ToString() as ParentIDPath,descr from dbo.emph2 where idpath.GetLevel()=2
2，深度優先遍歷是指遍歷一個節點的所有子節點
select idpath.ToString() as IDPath,id,parentid.ToString() as ParentIDPath,descr from dbo.emph2 where idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1
參考文件：