テーブル定義の例

CREATE TABLE [dbo].[Employee](
    [ID] [int] IDENTITY(1,1),
    [Name] [nvarchar](50),
    [Position] [hierarchyid],
)

初期データの投入

INSERT INTO Employee(Name,Position) VALUES('社长','/')
INSERT INTO Employee(Name,Position) VALUES('営業部長','/1/')
INSERT Employee(Name,Position) VALUES('技術部長','/2/')
INSERT Employee(Name,Position) VALUES('営業一郎','/1/1/')
INSERT Employee(Name,Position) VALUES('営業二郎','/1/2/')
INSERT Employee(Name,Position) VALUES('営業三郎','/1/3/')
INSERT Employee(Name,Position) VALUES('技術一郎','/2/1/')

データ確認クエリ

SELECT *,Position.ToString(),Position.GetLevel()
FROM Employee

主要メソッドの解説

ToStringメソッド：パスの文字列表現を取得

SELECT Name, Position.ToString() AS Path
FROM Employee

GetLevelメソッド：ノードの深さ（レベル）を取得

SELECT Name, Position.GetLevel() AS Level
FROM Employee

SELECT Name, Position.GetLevel() AS Level
FROM Employee
WHERE Position.GetLevel() = 1

GetAncestorメソッド：指定したレベル先祖ノードを取得

DECLARE @TargetNode hierarchyid
SELECT @TargetNode = Position FROM Employee WHERE Name = '社长'
SELECT * FROM Employee WHERE Position.GetAncestor(2) = @TargetNode

GetDescendantメソッド：子ノードを挿入するための位置を計算

DECLARE @ParentNode hierarchyid
DECLARE @LeftChild hierarchyid
DECLARE @RightChild hierarchyid

SELECT @ParentNode = Position FROM Employee WHERE Name = '社长'
SELECT @LeftChild = Position FROM Employee WHERE Name = '営業部長'
SELECT @RightChild = Position FROM Employee WHERE Name = '技術部長'

INSERT INTO Employee(Name,Position) 
VALUES('新部長',@ParentNode.GetDescendant(@LeftChild,@RightChild))

IsDescendantOfメソッド：先祖ノードからの子孫判定

DECLARE @RootNode hierarchyid
SELECT @RootNode = Position FROM Employee WHERE Name = '社长'
SELECT * FROM Employee WHERE Position.IsDescendantOf(@RootNode) = 1

GetReparentedValueメソッド：ノードツリー内の移動

DECLARE @MovingNode hierarchyid
DECLARE @OldParent hierarchyid
DECLARE @NewParent hierarchyid

SELECT @MovingNode = Position FROM Employee WHERE Name = '新部長'
SELECT @OldParent = Position FROM Employee WHERE Name = '社长'
SELECT @NewParent = Position FROM Employee WHERE Name = '技術部長'

UPDATE Employee 
SET Position = @MovingNode.GetReparentedValue(@OldParent, @NewParent) 
WHERE Position = @MovingNode

GetRootメソッド：ルートノードを取得

SELECT *
FROM Employee
WHERE Position = hierarchyid::GetRoot()

Parseメソッド：文字列からhierarchyidへの変換

DECLARE @PathString nvarchar(50)
SET @PathString = '/1/1/'
SELECT Name, Position.ToString() 
FROM Employee 
WHERE Position = hierarchyid::Parse(@PathString)