• 發行項

使用 hierarchyid 資料類型 (Database Engine)

hierarchyid 是系統提供的資料類型。使用 hierarchyid 做為資料類型來建立具有階層式結構的資料表，或參考另一個位置的階層式資料結構。透過 Transact-SQL，使用 hierarchyid 函數查詢及執行具有階層式資料的工作。

階層式資料的定義為一組資料項目，這些資料項目會依據階層式關聯性，彼此相關。階層式關聯性表示資料的一個項目是另一個項目的父代。階層式資料在資料庫中很常見。範例包括：

  • 組織結構

  • 檔案系統

  • 專案中的一組工作

  • 語言詞彙的分類表

  • 網頁之間的連結圖形

SQL Server 2008 中的新功能，hierarchyid 類型讓儲存與查詢階層式資料更容易。hierarchyid 最適合表示樹狀目錄，這是階層式資料最常見的類型。

hierarchyid 的主要屬性

hierarchyid 資料類型的值代表樹狀目錄階層中的位置。hierarchyid 的值具有下列屬性：

  • 極度壓縮

    代表樹狀目錄 (含有 n 個節點) 中之節點所需的平均位元數目會因平均 fanout (節點子系的平均數目) 而不同。若為小型 fanout (0-7)，其大小大約是 6*logAn 個位元，其中 A 是平均 fanout。在平均 fanout 為 6 個階層之 100,000 人員的組織階層中，節點會佔用大約 38 位元。然後，這會四捨五入成 40 位元 (或 5 個位元組)，以便進行儲存。

  • 比較是按照深度優先順序

    假設有兩個 hierarchyid 值 (a 和 b)，a<b 就表示在樹狀目錄的深度優先周遊中，a 在 b 前面。hierarchyid 資料類型的索引採用深度優先順序，而且在深度優先周遊中彼此接近的節點會以彼此接近的方式儲存。例如，某筆記錄的子系會儲存在該記錄旁。

  • 支援任意插入和刪除

    透過使用 GetDescendant 方法，您就一定能夠在任何指定節點的右側、任何指定節點的左側，或任何兩個同層級之間產生同層級。在階層中插入或刪除任意的節點數目時，比較屬性會保留下來。大部分的插入和刪除項目都會保留壓縮度屬性。不過，兩個節點之間的插入項目則會以稍微少的壓縮表示來產生 hierarchyid 值。

hierarchyid 的限制

hierarchyid 資料類型具有下列限制：

  • hierarchyid 類型的資料行不會自動代表樹狀目錄。應用程式負責決定是否要產生並指派 hierarchyid 值，以便讓資料列之間所需的關聯性反映在值中。有些應用程式甚至可能不要讓 hierarchyid 類型的資料行代表樹狀目錄。這些值也許是在另一個資料表中定義之階層位置的參考。

  • 應用程式負責管理產生與指派 hierarchyid 值的並行。除非應用程式使用唯一索引鍵條件約束或透過自己的邏輯強制本身的唯一性，否則，不保證資料行中的 hierarchyid 值是唯一的。

  • 由 hierarchyid 值代表的階層式關聯性不會像外部索引鍵關聯性般強制執行。如果 A 擁有子系 B，然後刪除 A 留下 B，讓關聯性變成不存在的記錄，這種階層式關聯性是可能發生而且有時候是恰當的。如果無法接受這種行為，應用程式必須在刪除父系前，查詢下階。

索引策略

編製階層式資料索引有兩種策略：

  • 深度優先

    如果是深度優先的索引，子樹的資料列會儲存在彼此的附近。例如，透過某位經理管理的所有員工都會儲存在經理記錄的附近。

沒有節點儲存在一起。

  • 廣度優先

    如果是廣度優先，會將資料列與階層的每個層級儲存在一起。例如，直接由相同經理管理之員工的記錄會儲存在彼此的附近。

每個階層會儲存在一起。

範例

GetLevel() 方法可以用來建立廣度優先排序。在下列範例中，會同時建立廣度優先和深度優先的索引：

USE AdventureWorks2008R2 ; 
GO

CREATE TABLE Organization
   (
    BusinessEntityID hierarchyid,
    OrgLevel as BusinessEntityID.GetLevel(), 
    EmployeeName nvarchar(50) NOT NULL
   ) ;
GO

在深度優先的索引中，節點子樹的所有節點都會位於相同位置。因此，深度優先的索引在回應關於子樹的查詢 (例如，「尋找此資料夾及其子資料夾中的所有檔案」) 時很有效率。

CREATE CLUSTERED INDEX Org_Breadth_First 
ON Organization(OrgLevel,BusinessEntityID) ;
GO

CREATE UNIQUE INDEX Org_Depth_First 
ON Organization(BusinessEntityID) ;
GO

在廣度優先的索引中，節點的所有直接子系都會位於相同位置。因此，廣度優先的索引在回應關於下層子系的查詢 (例如，「尋找直接回報給此經理的所有員工」) 時很有效率。

不論是讓深度優先、廣度優先，或是兩者，還是那個要產生叢集索引鍵 (如果有的話)，都取決於上述查詢類型的相對重要性，以及 SELECT 和 DML 作業的相對重要性。如需索引策略的詳細範例，請參閱＜教學課程：使用 hierarchyid 資料類型＞。

使用 hierarchyid 替代選項的時機

代表階層式資料的兩個 hierarchyid 替代選項為：

  • 父子式

  • XML

hierarchyid 通常優先於這些替代選項。但是，以下詳述替代選項可能優先於 hierarchyid 的特定情況。

父子式

使用父子式方式時，每個資料列都包含一個父系的參考。下表定義在父子關聯性中包含父系和子系資料列時所使用的一般資料表：

USE AdventureWorks2008R2 ;
GO

CREATE TABLE ParentChildOrg
   (
    BusinessEntityID int PRIMARY KEY,
    ManagerId int REFERENCES ParentChildOrg(BusinessEntityID),
    EmployeeName nvarchar(50) 
   ) ;
GO

比較父子式和 hierarchyid 的一般作業

  • 使用 hierarchyid 進行子樹查詢時，速度明顯加快。

  • 而使用 hierarchyid 進行直接下階查詢時，速度則稍慢。

  • 使用 hierarchyid 移動非分葉節點時，速度比較慢。使用 hierarchyid 插入非分葉節點與插入或移動分葉節點時，其複雜程度相同。

下列狀況存在時，最好使用父子式：

  • 索引鍵的大小相當重要。如果節點數目相同，hierarchyid 值會等於或大於整數系列 (smallint、int、bigint) 值。在極少的情況下，這是使用 [父子式] 的唯一原因，因為比起使用 [父子式] 結構時所需要的通用資料表運算式，hierarchyid 的 I/O 位置明顯較好，而且 CPU 的複雜性較高。

  • 查詢很少會查詢整個階層的區段。換句話說，如果查詢通常只處理階層中的單一點。在這些情況下，共同位置就不重要。例如，如果組織資料表僅用於執行個別員工的薪資，最好使用 [父子式]。

  • 非分葉的子樹會經常移動，因此效能非常重要。在父子式表示中，變更資料列在階層中的位置會影響單一資料列。在 hierarchyid 使用方式中變更資料列的位置會影響 n 個資料列，其中 n 是要移動之子樹中的節點數目。

    如果這種非分葉子樹經常移動，效能就非常重要，但是大部分的移動都是在定義良好的階層層級進行，因此，請考慮將較高和較低的層級分割為兩個階層。這樣會全部移到較高階層的分葉層級中。例如，請考慮由服務主控之網站的階層。網站包含許多以階層方式排列的頁面。主控的網站可能會移到網站階層的其他位置，但是從屬的頁面很少會重新排列。這可能會透過下列方式表示：

    CREATE TABLE HostedSites 
   (
    SiteId hierarchyid, PageId hierarchyid
   ) ;
GO

XML

XML 文件是一個樹狀結構，因此，單一的 XML 資料類型執行個體可以代表一個完整的階層。在 SQL Server 中建立 XML 索引時，會在內部使用 hierarchyid 值來代表階層中的位置。

如果以下所有狀況成立，使用 XML 資料類型可能比較好：

  • 永遠會儲存與擷取完整的階層。

  • 應用程式使用的資料為 XML 格式。

  • 述詞搜尋會受到相當的限制，而且效能並不重要。

例如，如果應用程式追蹤多個組織，務必儲存與擷取完整的組織階層，而且不要在單一組織中查詢，下列表單的資料表才可能有意義：

CREATE TABLE XMLOrg 
    (
    Orgid int,
    Orgdata xml
    ) ;
GO

從父子式移轉到 hierarchyid

目前大部分的樹狀目錄都是使用 [父子式] 代表。從 [父子式] 結構移轉到使用 hierarchyid 的資料表最簡單的方式，就是使用暫存資料行或暫存資料表來追蹤每個階層層級的節點數。如需移轉 [父子式] 資料表的範例，請參閱＜教學課程：使用 hierarchyid 資料類型＞的第 1 課。

hierarchyid 的查詢轉換

為了將查詢階層的效能發揮到極致，SQL Server 會自動執行包含 hierarchyid 的三個查詢轉換。這些轉換的結果可以在已轉換之查詢的執行程序表輸出中看到。

GetAncestor 會轉換為範圍掃描和殘餘述詞

GetAncestor(n) 會提供節點的第 n 個上階。當兩個節點之間需要精確關聯性 (父系、子系、祖系等) 時，這個項目比常見的 IsDescendantOf 更好用。

例如，執行下列查詢以尋找直接主管為 @value 的所有員工：

DECLARE @value hierarchyid ;
SELECT * FROM AdventureWorks2008R2.HumanResources.Employee 
WHERE  OrganizationNode.IsDescendantOf(@value) = 1;