Clever Geek Handbook

MS SQL：hierarchyid-新しい方法での階層

今日では、DBMSの中で最も普及しているのはリレーショナルデータベースであり、その主なオブジェクトはテーブルとそれらの間の関係です。 テーブルは非常に優れており、データの保存と操作のほとんどのタスクを解決できます。 しかし、現実の世界では、ストレージを必要とするエンティティは常に表形式で表示されるわけではありません。 テーブル以外のこれらの非常に一般的なタイプのデータ構造の1つは、各データ要素に祖先と子孫があるツリー構造です。 このような構造の例としては、企業スタッフの構造があります。そこでは、ディレクター（ツリーのルート）、彼の代理人、特定の代理人に従属するヘッドを持つ部門、ヘッドに報告する部門の従業員がヘッドにいます。



このような構造をテーブルに格納する方法の1つは、各エンティティの追加フィールドを定義することです。これにより、何らかの方法で祖先が決定されます。 したがって、常に祖先を知ることができ、単純な列挙により、階層ツリー全体を復元できます。 これは非常に一般的な方法であり、テーブルでツリー階層を表示する必要があるすべての場所で使用されます。



ただし、MS SQL DBMSの開発者は、MS SQL 2008の新しいバージョンで、ツリー階層を実装するための新しいタイプのデータストレージ階層 IDを提供しています。



はじめに



階層IDタイプはデータベースのシステムタイプであり、そのサイズはツリーの構造（深さ）とノードの子孫の平均数に応じて異なります。 MSDNには次の計算が示されています。100,000人の6レベルの階層を持つツリーの場合、hierarchyidは38ビットを取り、データベースは40ビットまたは5バイトに丸めます。 hierarchyidが占有できる最大サイズは892バイトです。



テーブル作成



まず、特定の会社の従業員の階層を格納する非常に単純なテーブルを作成します。

CREATE TABLE Table_1（

hierarchyid NOT NULLを非表示にしました

userId int NOT NULL 、

userName nvarchar（50） NOT NULL 、

CONSTRAINT PK_Table_1 プライマリ キーが クラスター化されました

（

[非表示] ASC

）） *このソースコードは、 ソースコードハイライターで強調表示されました。


テーブルには、hierarchyid、従業員ID、および彼の名前が含まれます。 次に、このテーブルで次の階層の担当者を再作成しようとします。 

  1イワノフ
     2ペトロフ
         7スミルノフ
             8パプキン
     3シドロフ
     4ワセリン
         5クルグロフ
         6マス




階層を作成する



まず、hierrachiaのルートを作成します。

Table_1に挿入

値 （hierarchyid :: GetRoot（）、1、 'Ivanov' ） *このソースコードは、 ソースコードハイライターで強調表示されました。


hierarchyid :: GetRoot（）に注意してください-これは、常に階層ルートの識別子を返す静的メソッドです。



次に、子孫をルートレコードに追加します。

Id階層IDを 宣言します



Id = MAX （非表示）を選択します

Table_1 から

ここで、 hid.GetAncestor（1）= hierarchyid :: GetRoot（）



Table_1に挿入

値 （hierarchyid :: GetRoot（）。GetDescendant（@id、 null ）、2、 'Petrov' ）;



Id = MAX （非表示）を選択します

Table_1 から

ここで、 hid.GetAncestor（1）= hierarchyid :: GetRoot（）



Table_1に挿入

値 （hierarchyid :: GetRoot（）。GetDescendant（@id、 null ）、3、 'Sidorov' ）;



Id = MAX （非表示）を選択します

Table_1 から

ここで、 hid.GetAncestor（1）= hierarchyid :: GetRoot（）



Table_1に挿入

値 （hierarchyid :: GetRoot（）。GetDescendant（@id、 null ）、4、 'Vasechkin' ）;

*このソースコードは、 ソースコードハイライターで強調表示されました。


このコードでは、次のコードセクションが注目に値します。



表に残りのすべてのエントリを記入して、図を完成させます。

@phId hierarchyidを宣言する

select @phId =（ SELECT hid FROM Table_1 WHERE userId = 2）;



Id = MAX （非表示）を選択します

Table_1 から

ここで、 hid.GetAncestor（1）= @phId



Table_1に挿入

値 （@ phId.GetDescendant（@id、 null ）、7、 'Smirnov' ）;



select @phId =（ SELECT hid FROM Table_1 WHERE userId = 4）;



Id = MAX （非表示）を選択します

Table_1 から

ここで、 hid.GetAncestor（1）= @phId



Table_1に挿入

値 （@ phId.GetDescendant（@id、 null ）、5、 'Kruglov' ）;



Id = MAX （非表示）を選択します

Table_1 から

ここで、 hid.GetAncestor（1）= @phId



Table_1に挿入

値 （@ phId.GetDescendant（@id、 null ）、6、 'Squares' ）;



select @phId =（ SELECT hid FROM Table_1 WHERE userId = 7）;



Id = MAX （非表示）を選択します

Table_1 から

ここで、 hid.GetAncestor（1）= @phId



Table_1に挿入

値 （@ phId.GetDescendant（@id、 null ）、8、 'Pupkin' ）;

*このソースコードは、 ソースコードハイライターで強調表示されました。


記事内のすべてのコードは1つのスクリプトで実行する必要があることに注意してください。


このコードをすべて実行すると、次の図が表示されます

select hid.ToString（）、hid.GetLevel（）、* from Table_1 *このソースコードは、 ソースコードハイライターで強調表示されました。 


 / 0 0x 1イワノフ
 / 1/1 0x58 2ペトロフ
 / 1/1/2 0x5AC0 7スミルノフ
 / 1/1/1/3 0x5AD6 8パプキン
 / 2/1 0x68 3シドロフ
 / 3/1 0x78 4ワセリン
 / 3/1/2 0x7AC0 5ラウンド
 / 3/2/2 0x7B40 6正方形

 WIN-Z6U4ALRNDSU（WIN-Z6U4ALRNDSU \ Administrator）：（8行が影響を受けます）


明らかに、選択されたデータは階層の構造を正確にコピーし、正しくソートされたデータを取得するために、parentIdを持つフィールドを介して一般に受け入れられている方法で階層が実装される場合に必要ないくつかのトリックに頼る必要はありませんでした。



ヘルパー関数



階層IDを使用する場合、MS SQL 2008にはいくつかの機能があります。 ここで、それぞれの目的を簡単に説明します。完全な説明については、ドキュメントを参照することをお勧めします。



おわりに



MS SQL 2008は、ツリー階層であるデータを保存する新しい方法を提供します。 これらの目的のために、階層要素への「パス」を含む階層IDタイプが導入されています。 データベースプログラマを支援するために、階層の表示の整理、データの移動、要素へのアクセス、新しい階層要素の追加を可能にする一連の補助機能が提供されます。



私の意見では、提案されたオプションは、parentIdフィールド（祖先）を介して階層データを保存する標準的な方法の優れた代替手段です。 hierarchyidを使用すると、最小限のコードを記述して階層構造を作成できます。


More articles:


All Articles