階層構造を持つデータを効率的に扱うため、SQL Serverではhierarchyid型が提供されている。この型は可変長で、ツリー構造内のノードまでのパスを格納する。値の生成と割り当てはアプリケーション側で行い、親子関係を構築する。

hierarchyidは深さ優先順序での比較をサポートする。2つの値xとyについてx < yは、深さ優先探索時にxがyより先に到達されることを意味する。つまり値が小さいほど根に近い。インデックスも深さ優先、左から右の順で構築される。同じ深さでは値の小さいノードが左側に配置される。

値の設定方法

パスはルート/から始まり、/で各ノードを区切る。正の整数または小数でノードを識別し、末尾も/で終わる。例: /, /2/5/, /1/3.5/7/

3通りの初期化方法がある。

declare @nodeA hierarchyid;
declare @nodeB hierarchyid;
declare @nodeC hierarchyid;

set @nodeA = '/2/5/8/';
set @nodeB = hierarchyid::Parse('/2/5/8/');
set @nodeC = 0x5B5E;

select @nodeA as A, @nodeB.ToString() as B, @nodeC.ToString() as C;

順序比較の動作

深さ優先探索での出現順序に基づく比較を示す。

declare @root hierarchyid;
declare @child1 hierarchyid;
declare @child2 hierarchyid;

set @root = hierarchyid::Parse('/2/5/');
set @child1 = hierarchyid::Parse('/2/5/9/');
set @child2 = hierarchyid::Parse('/2/5/10/');

select iif(@root < @child1, '先', '後') as root_vs_child1,
       iif(@child1 < @child2, '先', '後') as child1_vs_child2;

主要なメソッド

階層レベルの取得

GetLevel()でルートからの深さを取得する。

declare @sample hierarchyid = hierarchyid::Parse('/2/5/8/11/');
select @sample.GetLevel() as depth_from_root;

ルートノードの参照

静的メソッドGetRoot()でルートを取得する。

select hierarchyid::GetRoot().ToString() as path_string,
       hierarchyid::GetRoot() as binary_value;

子ノードの生成

GetDescendant(leftSibling, rightSibling)で指定した位置間の子ノードを生成する。

declare @parent hierarchyid;
declare @leftNode hierarchyid;
declare @rightNode hierarchyid;

set @parent = hierarchyid::Parse('/2/5/');
set @leftNode = hierarchyid::Parse('/2/5/4/');
set @rightNode = hierarchyid::Parse('/2/5/7/');

select @parent.GetDescendant(null, null).ToString() as first_child,
       @parent.GetDescendant(@leftNode, null).ToString() as after_left,
       @parent.GetDescendant(@leftNode, @rightNode).ToString() as between_both;

既存ノード間に挿入する場合、小数を利用できる。

declare @mid hierarchyid;
set @leftNode = hierarchyid::Parse('/2/5/4/');
set @rightNode = hierarchyid::Parse('/2/5/5/');
set @mid = hierarchyid::Parse('/2/5/');

select @mid.GetDescendant(@leftNode, @rightNode).ToString(); -- /2/5/4.1/ など

子孫関係の判定

IsDescendantOf(ancestor)で祖先関係を確認する。該当すれば1、そうでなければ0を返す。

declare @ancestor hierarchyid = hierarchyid::Parse('/2/5/');
declare @descendant hierarchyid = hierarchyid::Parse('/2/5/8/');
declare @unrelated hierarchyid = hierarchyid::Parse('/3/1/');

select @descendant.IsDescendantOf(@ancestor) as is_child,
       @unrelated.IsDescendantOf(@ancestor) as not_child;

データ更新時の注意点

hierarchyidには以下の制約がある。

• 自動的なツリー管理は行わない。アプリケーションで値を生成・管理する必要がある
• 一意性は自動保証されない。必要に応じてUNIQUE制約またはアプリケーションロジックで制御する
• 参照整合性は強制されない。親削除後も子が残る可能性がある

テーブル設計例

create table dbo.deptree
(
    nodepath hierarchyid not null primary key,
    empid int not null,
    empname nvarchar(50),
    superpath as nodepath.GetAncestor(1) persisted
        foreign key references dbo.deptree(nodepath)
);

ノードの移動と削除

子ノードを持つノードを削除・移動する場合、子ノードのパスを先に更新する必要がある。

-- ノード '/2/5/8/' の子を '/2/6/3/' 配下へ移動
update t
set nodepath = hierarchyid::Parse('/2/6/3/' + cast(empid as varchar) + '/')
from dbo.deptree t
where t.nodepath.IsDescendantOf(hierarchyid::Parse('/2/5/8/')) = 1
  and t.nodepath != hierarchyid::Parse('/2/5/8/');

-- 元のノードを削除
delete from dbo.deptree where nodepath = hierarchyid::Parse('/2/5/8/');

特定位置へのノード挿入

順序を維持しながらノードを挿入する。

declare @newEmpId int = 99;
declare @newName nvarchar(50) = N'新規担当';
declare @leftPath nvarchar(50) = '/2/5/8/20/';
declare @rightPath nvarchar(50) = '/2/5/8/21/';
declare @parentPath nvarchar(50) = '/2/5/8/';

declare @newPath hierarchyid;
set @newPath = hierarchyid::Parse(@parentPath).GetDescendant(
    hierarchyid::Parse(@leftPath),
    hierarchyid::Parse(@rightPath)
);

insert into dbo.deptree(nodepath, empid, empname)
values (@newPath, @newEmpId, @newName);

ツリー走査

幅優先探索（同じ階層の抽出）

select nodepath.ToString() as path_str, empid, empname
from dbo.deptree
where nodepath.GetLevel() = 3
order by nodepath;

深さ優先探索（子孫の全抽出）

select nodepath.ToString() as path_str, empid, empname
from dbo.deptree
where nodepath.IsDescendantOf(hierarchyid::Parse('/2/5/')) = 1
order by nodepath;

この結果、深さ優先でソートされた子孫ノードが取得できる。