SQL SERVER 2008 Hierarchyid數據類型

以往我們在關系數據庫中建立樹狀結構的時候，通常使用ID+ParentID來實現兩條紀錄間的父子關系。但這種方式只能標示其相對位置。解決這類問題在SqlServer2005出現之前通常是采用游標來操作，但熟悉數據庫內部機制的人都知道使用游標帶來的性能問題和其他問題是比較嚴重的。

到了SqlServer2005下，可以選擇用CTE來做遞歸查詢，這種方式查詢比較簡練，但由於數據庫內部是采用遞歸查詢的方式，其效率依舊不高；為了能夠實現既簡練又高效的查詢，通常的做法是增加冗余字段，比如增加一個"Path"字段，查詢時用模糊查詢來進行左匹配。對Path建索引後，這種查詢的效率還是相當高的，因此這種方式也是一種常規的設計方式；

SQL SERVER 2008引入了新的hierarchyid數據類型,可以用它來做本地存儲並且在樹層次結構中管理其位置.只用這個函數能簡潔地表示層次結構中的位置.該函數提供的一些內置的函數方法可以操作和遍歷層次結構,使得存儲和查詢分層數據更為容易,而不需要像那樣通過CTE遞歸來獲得. 

Hierarchyid類型其實是一個CLR自定義數據類型依次打開:數據庫->系統數據庫->master->可編程性->類型->系統數據類型->CLR數據類型->hierarchyid,可以看到該數據類型. 

於hierarchyid有關的一些函數主要有: 
GetAncestor ：取得某一個級別的祖先
GetDescendant ：取得某一個級別的子代
GetLevel ：取得級別
GetRoot ：取得根
IsDescendantOf ：判斷某個節點是否為某個節點的子代
Parse ：將字符串轉換為hierarchyid。該字符串的格式通常都是/1/這樣的
Read ：Read 從傳入的BinaryReader 讀取SqlHierarchyId 的二進制表示形式，並將SqlHierarchyId 對象設置為該值。不能使用Transact-SQL 調用Read。請改為使用CAST 或CONVERT。
GetReparentedValue ：可以用來移動節點（或者子樹）
ToString ：將hierarchyid轉換為字符串，與parse正好相反
Write : 將SqlHierarchyId 的二進制表示形式寫出到傳入的BinaryWriter 中。無法通過使用Transact-SQL 來調用Write。請改為使用CAST 或CONVERT。

hierarchyid 數據類型的值表示樹層次結構中的位置。hierarchyid 的值具有以下屬性： 

非常緊湊 

在具有 n 個節點的樹中，表示一個節點所需的平均位數取決於平均端數（節點的平均子級數）。端數較小時 (0-7)，大小約為 6*logAn 位，其中 A 是平均端數。對於平均端數為 6 級、包含 100,000 個人的組織層次結構，一個節點大約占 38 位。存儲時，此值向上捨入為 40 位，即 5 字節。


按深度優先順序進行比較 

給定兩個 hierarchyid 值 a 和 b，a Ravi 

| | 

Ben<-> Laura Vijay <-> Frank <-> James 

Use AdventureWorksLT 
Go  
--Scheme Creation 
Create Schema HumanResources 
Go  
--Table Creation 
CREATE TABLE HumanResources.EmployeeDemo 
(
OrgNode HIERARCHYID,
EmployeeID INT,
LoginID VARCHAR(100),
Title VARCHAR(200),
HireDate DATETIME
)
Go 
--Index Creation 
CREATE UNIQUE CLUSTERED INDEX idxEmployeeDemo 
ON HumanResources.EmployeeDemo (OrgNode,EmployeeID)


下面插入一些數據


SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)  
LUES (hierarchyid::GetRoot(), 1,'adventure-works\scott', 'CEO', '3/11/05') ; 
      
CLARE @Manager hierarchyid    
LECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo; 
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)     
LUES (@Manager.GetDescendant(NULL,NULL), 2, 'adventure-works\Mark', 'CTO', '4/05/07')       
 


CLARE @Manager hierarchyid 
CLARE @FirstChild hierarchyid  
LECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo; 
lect @FirstChild = @Manager.GetDescendant(NULL,NULL)        
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)        
LUES (@Manager.GetDescendant(@FirstChild,NULL), 3, 'adventure-works\ravi', 'Director Marketing', '4/08/07')        

           
Insert the First Descendant of a Child Node    
CLARE @Manager hierarchyid     
LECT @Manager = CAST('/1/' AS hierarchyid)       
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) 
LUES (@Manager.GetDescendant(NULL, NULL),45,   'adventure-works\Ben','Application Developer', '6/11/07') ;         

  
Insert the Second Descendant of a Child Node 
CLARE @Manager hierarchyid        
CLARE @FirstChild hierarchyid         
LECT @Manager = CAST('/1/' AS hierarchyid)   
LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)    

SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)      
LUES (@Manager.GetDescendant(@FirstChild, NULL),55,  'adventure-works\Laura','Trainee Developer', '6/11/07') ;           

           
Insert the first node who is the Descendant of Director Marketing      
CLARE @Manager hierarchyid     
CLARE @FirstChild hierarchyid     
LECT @Manager = CAST('/2/' AS hierarchyid)          

SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)   
LUES (@Manager.GetDescendant(NULL, NULL),551, 'adventure-works\frank','Trainee Sales Exec.', '12/11/07') ;        

          
Insert the second node who is the Descendant of Director Marketing     
CLARE @Manager hierarchyid           
CLARE @FirstChild hierarchyid        
LECT @Manager = CAST('/2/' AS hierarchyid)   
LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) 
LUES (@Manager.GetDescendant(@FirstChild, NULL),531, 'adventure-works\vijay','Manager Industrial Sales', '12/09/06') ;         

    
Insert the third node who is the Descendant of Director Marketing        
in between 2 existing descendants    
CLARE @Manager hierarchyid    
CLARE @FirstChild hierarchyid      
CLARE @SecondChild hierarchyid     
LECT @Manager = CAST('/2/' AS hierarchyid)          
LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)   
LECT @SecondChild = @Manager.GetDescendant(@FirstChild,NULL)   
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)  
LUES (@Manager.GetDescendant(@FirstChild, @SecondChild),543,  'adventure-works\james','Manager Consumer Sales', '12/04/06') ;           



Hierarchyid字段類型提供了一系列相關查詢函數，可以方便的查詢父子關系數據。下面我們查詢下數據

DECLARE @TID hierarchyid 
SELECT @TID=OrgNode FROM HumanResources.EmployeeDemo  WHERE title='cto' 

SELECT *, OrgNode.GetLevel() as 層次,OrgNode.ToString() as 路徑  FROM HumanResources.EmployeeDemo WHERE @TID.IsDescendantOf(OrgNode)=1 

SELECT *, OrgNode.GetLevel() as 層次,OrgNode.ToString() as 路徑  FROM HumanResources.EmployeeDemo WHERE  OrgNode.IsDescendantOf(@TID)=1 

下面另外附幾個操作的存儲過程：


向表裡插入記錄


SET QUOTED_IDENTIFIER ON
GO
--Use Serializable Transaction 
CREATE PROCEDURE [dbo].[AddEmployee](@ManagerID hierarchyid, @EmpID int,
@LogID varchar(100), @JobTitle as varchar(200), @JoiningDate datetime)
AS 
BEGIN 

DECLARE @LastChild hierarchyid 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
BEGIN TRANSACTION
SELECT @LastChild = Max(OrgNode) From HumanResources.EmployeeDemo 
WHERE OrgNode = @ManagerID 
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) 
VALUES(@LastChild, @EmpID,@LogID , @JobTitle, @JoiningDate) 
COMMIT 
END ;

移動層級關系


CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) ) 
AS 
BEGIN

DECLARE @nold HierarchyID 
DECLARE @nnew HierarchyID 

SELECT @nold = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @oldMgr ; 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

BEGIN TRANSACTION 
SELECT @nnew = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @newMgr ; 
SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL) 
FROM HumanResources.EmployeeDemo WHERE OrgNode.GetAncestor(1)=@nnew ; 
UPDATE HumanResources.EmployeeDemo 
SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew) 
WHERE @nold.IsDescendantOf(OrgNode) = 1
 
COMMIT  TRANSACTION
END





獲取最大的子節點，傳遞給GetDescendant() 函數獲得新的子節點


Create Function GetMyMaxChild(@ManagerID as BigInt) Returns HierarchyID 
BEGIN 

Declare @ManagerNode HierarchyID 
Declare @MaxChild HierarchyID 
 --Get the ManagerNode 
Select @ManagerNode = OrgNode from 
HumanResources.EmployeeDemo Where EmployeeID = @ManagerID 
 --Get the Max Child 

Select @MaxChild = Max(OrgNode) from HumanResources.EmployeeDemo 
Where OrgNode.GetAncestor(1) = @ManagerNode 
--Return the Value 

RETURN @MaxChild 
END