SQL SERVER 2008 新資料庫類型hierarchyid

-2010-05-27(東升)

SQL SERVER 2008 新資料庫類型hierarchyid

    SQL SERVER 2008引入了新的hierarchyid資料類型,可以用它來做本地存儲并且在樹層次

結構中管理其位置.隻用這個函數能簡潔地表示層次結構中的位置.該函數提供的一些内置的函

數方法可以操作和周遊層次結構,使得存儲和查詢分層資料更為容易,而不需要像那樣通過

CTE遞歸來獲得.

    該類型其實是一個CLR自定義資料類型依次打開:資料庫->系統資料庫->master->可程式設計性

->類型->系統資料類型->CLR資料類型->hierarchyid,可以看到該資料類型.

    于hierarchyid有關的一些函數主要有:

    GetAncestor ：取得某一個級别的祖先

    GetDescendant ：取得某一個級别的子代

    GetLevel ：取得級别

    GetRoot ：取得根

    IsDescendantOf ：判斷某個節點是否為某個節點的子代

    Parse ：将字元串轉換為hierarchyid。該字元串的格式通常都是/1/這樣的

    Read ：Read 從傳入的BinaryReader 讀取SqlHierarchyId 的二進制表示形式，并将

           SqlHierarchyId 對象設定為該值。不能使用Transact-SQL 調用Read。請改

           為使用CAST 或CONVERT。

    GetReparentedValue ：可以用來移動節點（或者子樹）

    ToString ：将hierarchyid轉換為字元串，與parse正好相反

    Write : 将SqlHierarchyId 的二進制表示形式寫出到傳入的BinaryWriter 中。無法通

           過使用Transact-SQL 來調用Write。請改為使用CAST 或CONVERT。

    以下就該新類型做一些示範

*/

    USE TESTDB

    GO

    CREATE TABLE TEST(

           [PHASE] hierarchyid NOT NULL,

           LVL AS [PHASE].GetLevel(),

           USERID INT NOT NULL,

           USERNAME VARCHAR(50) NOT NULL

           )

    --接着插入一個根

    INSERT TEST([PHASE],USERID,USERNAME)

    VALUES('/',1001,'東升')--'/'被用來表示層次的根,會自動轉換成二進制格式

    SELECT * FROM TEST

/* 結果

    PHASE LVL    USERID     USERNAME

    ------ ------ --------- ----------

    0x     0      1001       東升

    (1 行受影響)

    查詢後應該發現'/'被重新定義成x十六進制值.

    使用斜杠字元來表示層次路徑,一個表示的是根,用斜杠分隔的整數值來組成連續的層次.

    插入小弟

    VALUES('/1/',1002,'洋芋')

    VALUES('/2/',1003,'紅太狼')

    --以上條資料時同級别的

/*

    PHASE     LVL    USERID      USERNAME

    -------    ------ --------- -----------

    0x         0      1001        東升

    0x58       1      1002        洋芋

    0x68       1      1003        紅太狼

    (3 行受影響)

    --同樣可以使用GetDescendant方法來填充[PHASE]

    DECLARE    @PARENTPHASE hierarchyid, @Child1 hierarchyid

    SELECT @PARENTPHASE= CONVERT(hierarchyid,'/1/')

    VALUES(@PARENTPHASE.GetDescendant(NULL,NULL),1004,'洋芋小弟1')

    SELECT @Child1 = CAST('/1/1/' AS hierarchyid)

    VALUES(@PARENTPHASE.GetDescendant(@Child1,NULL),1005,'洋芋小弟2')

    SELECT [PHASE].ToString() AS [PHASE] ,LVL,USERID,USERNAME FROM TEST

    ORDER BY [PHASE]