다음을 통해 공유


hierarchyid 데이터 작업

이 항목에는 계층적 데이터 트리를 관리하고 쿼리하는 일반적인 작업에 대한 정보가 포함되어 있습니다.

항목 내용

hierarchyid를 사용하여 트리 관리

트리 강제 적용

CLR 사용 예

하위 트리 이동

hierarchyid를 사용하여 트리 관리

hierarchyid 열이 반드시 트리를 나타내는 것은 아니지만 응용 프로그램에서 손쉽게 해당 열이 트리를 나타내도록 만들 수 있습니다.

  • 새 값을 생성할 때 다음 중 하나를 수행합니다.

    • 부모 행의 마지막 자식 번호를 추적합니다.

    • 마지막 자식을 계산합니다. 이 작업을 효율적으로 수행하려면 너비 우선 인덱스가 필요합니다.

  • 클러스터링 키의 일부 등으로 열에 대한 고유 인덱스를 만들어 고유성을 강제 적용합니다. 고유 값이 삽입되게 하려면 다음 중 하나를 수행합니다.

    • 새 자식 노드 각각의 고유성을 확인하고 직렬화 가능 트랜잭션에서 해당 노드를 삽입합니다.

    • 고유 키 위반 오류를 검색하고 다시 시도합니다.

오류 검색 사용 예

다음 예에서 예제 코드는 새 자식 EmployeeId 값을 계산한 다음 키 위반을 검색하고 INS_EMP 표식으로 돌아와 새 행에 대해 EmployeeId 값을 다시 계산합니다.

USE AdventureWorks2008R2;
GO

CREATE TABLE Org_T1
   (
    EmployeeId hierarchyid PRIMARY KEY,
    OrgLevel AS EmployeeId.GetLevel(),
    EmployeeName nvarchar(50) 
   ) ;
GO

CREATE INDEX Org_BreadthFirst ON Org_T1(OrgLevel, EmployeeId)
GO

CREATE PROCEDURE AddEmp(@mgrid hierarchyid, @EmpName nvarchar(50) ) 
AS
BEGIN
    DECLARE @last_child hierarchyid
INS_EMP: 
    SELECT @last_child = MAX(EmployeeId) FROM Org_T1 
    WHERE EmployeeId.GetAncestor(1) = @mgrid
INSERT Org_T1 (EmployeeId, EmployeeName)
SELECT @mgrid.GetDescendant(@last_child, NULL), @EmpName 
-- On error, return to INS_EMP to recompute @last_child
IF @@error <> 0 GOTO INS_EMP 
END ;
GO

직렬화 가능 트랜잭션 사용 예

Org_BreadthFirst 인덱스를 사용하면 @last_child 확인 시 범위 검색이 수행됩니다. 응용 프로그램에서 확인할 수 있는 다른 오류 상황뿐만 아니라 삽입 후의 중복 키 위반은 ID가 같은 여러 직원을 추가하려고 했음을 나타내므로 @last_child를 다시 계산해야 합니다. 다음 코드에서는 직렬화 가능 트랜잭션과 너비 우선 인덱스를 사용하여 새 노드 값을 계산합니다.

CREATE TABLE Org_T2
    (
    EmployeeId hierarchyid PRIMARY KEY,
    LastChild hierarchyid, 
    EmployeeName nvarchar(50) 
    ) ;
GO

CREATE PROCEDURE AddEmp(@mgrid hierarchyid, @EmpName nvarchar(50)) 
AS
BEGIN
DECLARE @last_child hierarchyid
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 

UPDATE Org_T2 
SET @last_child = LastChild = EmployeeId.GetDescendant(LastChild,NULL)
WHERE EmployeeId = @mgrid
INSERT Org_T2 (EmployeeId, EmployeeName) 
    VALUES(@last_child, @EmpName)
COMMIT
END ;

다음 코드에서는 테이블을 3개의 행으로 채우고 결과를 반환합니다.

INSERT Org_T2 (EmployeeId, EmployeeName) 
    VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2

결과 집합은 다음과 같습니다.

EmployeeId LastChild EmployeeName
---------- --------- ------------
0x        0x58       David
0x58      0x5AC0     Sariya
0x5AC0    NULL       Mary

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘맨 위로 이동

트리 강제 적용

위 예에서는 응용 프로그램에서 트리가 유지되도록 하는 방법을 보여 줍니다. 제약 조건을 통해 트리를 강제 적용하려면 기본 키 ID에 FOREIGN KEY 제약 조건을 다시 적용하여 각 노드의 부모를 정의하는 계산 열을 만듭니다.

CREATE TABLE Org_T3
(
   EmployeeId hierarchyid PRIMARY KEY,
   ParentId AS EmployeeId.GetAncestor(1) PERSISTED  
      REFERENCES Org_T3(EmployeeId),
   LastChild hierarchyid, 
   EmployeeName nvarchar(50)
)
GO

계층적 트리를 유지하도록 트러스트되지 않은 코드에 해당 테이블에 대한 직접 DML 액세스가 있는 경우 관계를 이러한 방법으로 강제 적용하는 것이 좋습니다. 모든 DML 작업에서 제약 조건을 확인해야 하므로 이 방법은 성능을 저하시킬 수 있습니다.

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘맨 위로 이동

CLR 사용 예

한 계층의 두 노드를 사용하는 일반적인 작업에는 수준이 가장 낮은 공통 상위 항목을 찾는 작업이 있습니다. hierarchyid 형식을 Transact-SQL 또는 CLR에서 모두 사용할 수 있으므로 두 방법을 모두 사용하여 이러한 작업을 코드로 작성할 수 있습니다. 그러나 성능이 향상되는 CLR을 사용하는 것이 좋습니다.

다음 CLR 코드를 사용하여 상위 항목을 찾아 나열하고 수준이 가장 낮은 공통 상위 항목을 찾을 수 있습니다.

using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;

public partial class HierarchyId_Operations
{
    [SqlFunction(FillRowMethodName = "FillRow_ListAncestors")]
    public static IEnumerable ListAncestors(SqlHierarchyId h)
    {
        while (!h.IsNull)
        {
            yield return (h);
            h = h.GetAncestor(1);
        }
    }
    public static void FillRow_ListAncestors(Object obj, out SqlHierarchyId ancestor)
    {
        ancestor = (SqlHierarchyId)obj;
    }

    public static HierarchyId CommonAncestor(SqlHierarchyId h1, HierarchyId h2)
    {
        while (!h1.IsDescendant(h2))
            h1 = h1.GetAncestor(1);
        
        return h1;
    }
}

다음 Transact-SQL 예에서 ListAncestorCommonAncestor 메서드를 사용하려면 다음과 유사한 코드를 실행하여 SQL Server에서 DLL을 빌드하고 HierarchyId_Operations 어셈블리를 만듭니다.

CREATE ASSEMBLY HierarchyId_Operations 
FROM '<path to DLL>\ListAncestors.dll'
GO

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘맨 위로 이동

상위 항목 나열

노드의 상위 항목 목록을 만드는 것은 조직에서의 위치 표시와 같은 일반적인 작업입니다. 이를 수행할 수 있는 한 가지 방법은 위에 정의된 HierarchyId_Operations 클래스를 사용하는 테이블 반환 함수를 사용하는 것입니다.

Transact-SQL 사용:

CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO

사용 예:

DECLARE @h hierarchyid
SELECT @h = OrgNode 
FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\janice0' -- /1/1/5/2/

SELECT LoginID, OrgNode.ToString() AS LogicalNode
FROM HumanResources.EmployeeDemo AS ED
JOIN ListAncestors(@h) AS A 
   ON ED.OrgNode = A.Node
GO

수준이 가장 낮은 공통 상위 항목 찾기

위에 정의된 HierarchyId_Operations 클래스를 통해 다음 Transact-SQL 함수를 만들어 한 계층의 두 노드를 사용하는 수준이 가장 낮은 공통 상위 항목을 찾습니다.

CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO

사용 예:

DECLARE @h1 hierarchyid, @h2 hierarchyid

SELECT @h1 = OrgNode 
FROM  HumanResources.EmployeeDemo 
WHERE LoginID = 'adventure-works\jossef0' -- Node is /1/1/3/

SELECT @h2 = OrgNode 
FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\janice0' -- Node is /1/1/5/2/

SELECT OrgNode.ToString() AS LogicalNode, LoginID 
FROM HumanResources.EmployeeDemo  
WHERE OrgNode = dbo.CommonAncestor(@h1, @h2) ;

결과 노드는 /1/1/입니다.

맨 위로 이동 링크와 함께 사용되는 화살표 아이콘맨 위로 이동

하위 트리 이동

다른 일반적인 작업은 하위 트리를 이동하는 것입니다. 아래 절차에서는 @oldMgr의 하위 트리를 사용하여 이 하위 트리(@oldMgr 포함)를 @newMgr의 하위 트리로 만듭니다.

CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) )
AS
BEGIN
DECLARE @nold hierarchyid, @nnew hierarchyid
SELECT @nold = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @oldMgr ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @nnew = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @newMgr ;

SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL) 
FROM HumanResources.EmployeeDemo WHERE OrgNode.GetAncestor(1)=@nnew ;

UPDATE HumanResources.EmployeeDemo  
SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew)
WHERE OrgNode.IsDescendantOf(@nold) = 1 ; 

COMMIT TRANSACTION
END ;
GO