RiCo技術農場

SQL2008推出hierarchyid資料類型，該類型長度是可變的，用來儲存階層結構資料，建立父子節點之間的關聯。

資料比較是按照深度優先，兩個hierarchyID(a和b)，假設a<b，即先找a在找b，也就是說值越小越接近root node(/)。

CLR type為SqlHierarchyId，但該型別對應看來.net core 2.1有些限制

Support hierarchyid and user defined types #365

Scaffolding literals for SqlHierarchyId (and possibly other UDTs) doesn't work

目前SqlHierarchyid只能在.NET Framework執行，.NET Core不能執行，

這真叫人傷心，因為hierarchyid查詢樹狀資料效能，會比CTE遞迴來的好，

但資料更新方面需額外注意，下面我簡單示範

Hierarchyid需”/”開頭”/”結尾，中間使用數字標示一個元素，如/1/2/3/

declare @myhiera HierarchyID
declare @myhierb HierarchyID
declare @myhierc HierarchyID

set @myhiera='/11/21/31/'
set @myhierb=HierarchyID::Parse('/11/21/31/')
set @myhierc=@myhiera

select @myhiera as c1,@myhierb.ToString() as c2,@myhierc.ToString() as c3

我個人常用方法

create table myEmployeeH (
    ID INT UNIQUE NOT NULL,
	Name VARCHAR(50) NOT NULL,
    Node HierarchyID ,
    NodeLevel as Node.GetLevel(),  -- GetLevel 節點深度
	NodeStringPath as (Node.ToString())
    )
create unique index UIX_NodeLevel on myEmployeeH (NodeLevel,Node)
go
GetRoot():靜態方法，回傳樹狀結構的root  
insert into myEmployeeH (ID,Name,Node)
       values (1, 'rico',HierarchyId::GetRoot())

GetDescendant():傳回父系子節點
declare @parent hierarchyid = hierarchyid::GetRoot()
   insert into myEmployeeH (ID,Name,Node) 
   values
   (2,'ricoisme',@parent.GetDescendant(null,null))—參數表示左和右節點
Go
declare @parent hierarchyid = (select Node from myEmployeeH where name = 'rico')
   declare @ricoisme hierarchyid = (select Node from myEmployeeH where name = 'ricoisme')
   insert into myEmployeeH (ID,Name,Node) values (3, 'Fifi',@parent.GetDescendant(@ricoisme,null))
   go
    declare @parent hierarchyid = (select Node from myEmployeeH where name = 'rico')
   declare @Fifi hierarchyid = (select Node from myEmployeeH where name = 'Fifi')
   insert into myEmployeeH (ID,Name,Node) values(4, 'Sherry',@parent.GetDescendant(@Fifi,null))
   go

     declare @parent hierarchyid = (select Node from myEmployeeH where name = 'ricoisme')
  insert into myEmployeeH (ID,Name,Node) 
   values
   (5,'r1',@parent.GetDescendant(null,null))
   go
    declare @parent hierarchyid = (select Node from myEmployeeH where name = 'ricoisme')
	 declare @r1 hierarchyid = (select Node from myEmployeeH where name = 'r1')
  insert into myEmployeeH (ID,Name,Node) 
   values
   (6,'r1-1',@parent.GetDescendant(@r1,null))
   go
    declare @parent hierarchyid = (select Node from myEmployeeH where name = 'r1-1')
	 insert into myEmployeeH (ID,Name,Node) 
   values
   (7,'r1-1-A',@parent.GetDescendant(null,null))
   go
    declare @parent hierarchyid = (select Node from myEmployeeH where name = 'r1-1')
	 declare @r11A hierarchyid = (select Node from myEmployeeH where name = 'r1-1-A')
	 insert into myEmployeeH (ID,Name,Node) 
   values
   (8,'r1-1-AB',@parent.GetDescendant(@r11A,null))
   Go
select * from myEmployeeH

目前最大階層數=3

 

GetAncestor:代表this的第n階層

IsDescendantOf ( parent ):若為父系，return true

Parse:標準hierarchyID字串轉換成hierarchyID值，作用和ToString相反

--找出階層/1/的子樹狀資料
select [Parent]=(Node.GetAncestor(1).ToString())
,* 
from myEmployeeH f1 
where f1.Node.IsDescendantOf(HierarchyID::Parse('/1/')) = 1

--階層/1/2/的子樹狀資料

 

更新/1/2/節點成為/1/3/和相關子節點

--update parent
   update myEmployeeH set node=HierarchyID::Parse('/1/3/')
   where Node.GetAncestor(0).ToString()='/1/2/'

   --update childs
   update e set node=	
	HierarchyID::Parse( replace( left(Node.ToString(),5),'/1/2/','/1/3/')+ Right(Node.ToString(),len(Node.ToString())-5) )
	--HierarchyID::Parse('/1/2/'+cast(e.id as varchar)+'/')
	from myEmployeeH e
	where Node.IsDescendantOf(HierarchyID::Parse('/1/2/'))=1

--check
select [Parent]=(Node.GetAncestor(1).ToString())
,* 
from myEmployeeH f1 
where f1.Node.IsDescendantOf(HierarchyID::Parse('/1/3/')) = 1

PS:delete parent node資料，預設不會連帶刪除childe nodes，需要自行處理

 

和CTE比較一下查詢樹狀結構資料效能

create table myEmployee (
    ID INT UNIQUE NOT NULL,
	Name VARCHAR(50) NOT NULL,
    ParentId Int Null,
	Path varchar(100)
    )
create index IX_ParentId on myEmployee (ParentId)
go

insert into myEmployee values(1,'rico',null,'/')
,(2,'ricoisme',1,'/1/')
,(3,'Fifi',1,'/2/')
,(4,'Sherry',1,'/3/')
,(5,'r1',2,'/1/1/')
,(6,'r1-1',2,'/1/3/')
,(7,'r1-1-A',6,'/1/3/1/')
,(8,'r1-1-AB',6,'/1/3/2/')

set statistics io,time on
declare @Id int = 2;
with FolderHierarchy (id, name, path, parentid, level) as 
(
	select Id, Name, Path, ParentId, 1 as level from myEmployee where Id = @Id
	union all
	select f.Id, f.Name, f.Path, f.ParentId, level + 1 from myEmployee f
	inner join FolderHierarchy h
	on f.ParentId = h.Id
)
select * from FolderHierarchy order by level

  select * from myEmployeeH
   where Node.IsDescendantOf(HierarchyID::Parse('/1/'))=1

擷取部分

 

 

參考

hierarchyid data type method reference 

Hierarchy in TSQL – step by step guide

Use HierarchyID

SQL Server Hierarchical data

Get Downline and Upline of hierarchical data and Performance review - CTE vs HierarchyId in SQL Server 2008