hierarchyid データ型の使用 (データベース エンジン)

hierarchyid の主要な特性

hierarchyid データ型の値は、ツリー階層における位置を表します。hierarchyid の値には、以下の特性があります。

• 非常にコンパクト

  n 個のノードを持つツリー内の、1 つのノードを表すために必要な平均ビット数は、平均ファンアウト (ノードあたりの子の平均数) によって決まります。ファンアウトが小さい場合 (0 ～ 7)、サイズは約 6*logAn ビットです (A は平均ファンアウト)。平均ファンアウトが 6 レベルで、100,000 人から成る組織階層の場合、1 つのノードには約 38 ビットが必要です。格納時には、これが 40 ビット (5 バイト) に切り上げられます。

• 深さ優先順で比較

  a と b の 2 つの hierarchyid 値がある場合、a<b は、ツリーの深さ優先検査において a が b の前に来ることを意味します。hierarchyid データ型のインデックスは深さ優先順であり、深さ優先検査で近接するノードどうしは、相互に近接して格納されます。たとえば、あるレコードの子は、そのレコードに隣接して格納されます。

• 任意の挿入および削除のサポート

  GetDescendant メソッドを使用すると、指定したノードの右側や左側、または任意の 2 つの兄弟間に、いつでも兄弟を生成できます。階層に対して任意の数のノードを挿入または削除しても、比較の特性は維持されます。ほとんどの挿入や削除では、コンパクトさも維持されます。ただし、2 ノード間に挿入した場合は、hierarchyid 値のコンパクトさがやや失われます。

hierarchyid の制限事項

hierarchyid データ型には、以下の制限事項があります。

• hierarchyid 型の列が自動的にツリーを表すことはありません。行と行の間に必要なリレーションシップが反映されるよう、hierarchyid 値を生成して割り当てるのは、アプリケーションの役割です。アプリケーションによっては、hierarchyid 型の列でツリーを表すことが望ましくない場合もあります。値で、別のテーブルで定義された階層内の場所を参照することがあるためです。

• hierarchyid 値の生成と割り当てにおいて、同時実行を管理するのはアプリケーションの役割です。アプリケーションで一意キー制約を使用したり、独自のロジックで一意性を適用したりしない限り、列内の hierarchyid 値の一意性は保証されません。

• hierarchyid 値で表される階層リレーションシップは、外部キー リレーションシップとは適用方法が異なります。階層リレーションシップでは、A に子 B があるとき、A だけを削除し、存在しないレコードに対するリレーションシップを B が引き続き保持することも可能であり、これが適切な場合もあります。この動作を許容しない場合は、親を削除する前に、アプリケーションで子孫に対するクエリを実行する必要があります。

インデックス作成方法

階層データのインデックスを作成する方法には、次の 2 つがあります。

• 深さ優先

  深さ優先のインデックスで、サブツリー内の行が相互に近接して格納されます。たとえば、ある管理者の管理責任下にあるすべての従業員が、管理者のレコードの近くに格納されます。

• 幅優先

  幅優先の場合、階層の各レベルの行が一緒に格納されます。たとえば、同一の管理者に直属する従業員のレコードが、相互に近接して格納されます。

例

幅優先順を作成するには、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 を使用せずに階層データを表すためには、次の 2 つの方法があります。

• 親/子

• XML

通常、これらの方法よりも hierarchyid の方が優れています。しかし、次のような状況では、これらの代替方法を使用した方がよい場合があります。

親/子

USE AdventureWorks2008R2 ;
GO

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

XML

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

親/子から hierarchyid への移行

現在、ほとんどのツリーは親/子を使用して表されています。親/子構造から hierarchyid を使用したテーブルに移行する最も簡単な方法は、一時列または一時テーブルを使用して、階層の各レベルのノード数を追跡する方法です。親/子テーブルの移行例については、「チュートリアル : hierarchyid データ型の使用」のレッスン 1 を参照してください。

hierarchyid のクエリ変換

階層のクエリのパフォーマンスを最大化するため、SQL Server では、hierarchyid が含まれたクエリに 3 つの変換が自動的に実行されます。これらの変換の結果は、変換済みクエリのプラン表示出力で参照できます。

GetAncestor は範囲スキャンおよび残余述語に変換される

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

関連項目

参照

概念