SQL Server 階層データ (hierarchyid) のインデックス (深さ優先) が使われるかの確認

親子関係を hierarchyid で表して、自分含めて自分より下位のデータを検索することがあったので、ちょっと確認。

階層データ (SQL Server) - SQL Server | Microsoft Learn

とりあえずクエリはこんなの

drop table if exists [階層型テスト]
create table [階層型テスト] (
  [PK] bigint not null primary key
  , [階層PK] hierarchyid not null index [IX_階層型テスト_階層PK]
  , [備考] nvarchar(max)
)
;

begin tran
;
with [cte] as (
  select 0 as [seq] 
  union all select [seq] + 1 
  from [cte] where [seq] < 99999
)
insert into [階層型テスト] ([PK], [階層PK], [備考])
select 
  [seq]
  , case when [seq] < 10 then concat('/', [seq] ,'/')
         when [seq] < 100 then concat('/', [seq] / 10,'/', [seq], '/')
         when [seq] < 1000 then concat('/', [seq] / 100,'/', [seq] / 10, '/', [seq], '/')
         when [seq] < 10000 then concat('/', [seq] / 1000,'/', [seq] / 100, '/', [seq] / 10, '/', [seq], '/')
         else concat('/', [seq] / 10000,'/', [seq] / 1000,'/', [seq] / 100, '/', [seq] / 10, '/', [seq], '/')
    end
  , cast([seq] as nvarchar(max))
from [cte]
option (maxrecursion 0)
;

commit

select *, [階層PK].ToString() from [階層型テスト]

100000件 (0 ～ 99999 まで) のデータで、適当に階層化したテストデータ用意。

これに対して、hierarchyid の IsDescendantOf (データベース エンジン) - SQL Server | Microsoft Learn を使って検索する。

select [PK], [階層PK], [階層PK].ToString(), [備考] 
from [階層型テスト] 
where [階層PK].IsDescendantOf('/1/10/100/') = 1
-- covered query
select [PK], [階層PK], [階層PK].ToString()
from [階層型テスト] 
where [階層PK].IsDescendantOf('/1/10/100/') = 1

の実行計画がこれ。

結果は、共に 111 件で、ちゃんと作ったインデックス (IX_階層型テスト_階層PK) の シークになってるからOK。
covered じゃないクエリは、Key Lookup 発生するから、クラスター化インデックス も見てるのはしゃーなし。

子、孫が多すぎる場合は、covered じゃないクエリはインデックスを使わずに、クラスター化インデックスのスキャン (全件見る) になった。
このクエリは、1111 件返す。

select [PK], [階層PK], [階層PK].ToString(), [備考] 
from [階層型テスト] 
where [階層PK].IsDescendantOf('/1/10/') = 1
-- covered query
select [PK], [階層PK], [階層PK].ToString()
from [階層型テスト] 
where [階層PK].IsDescendantOf('/1/10/') = 1