다음을 통해 공유

계층적 데이터(SQL Server)

  • 아티클

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

기본 제공 hierarchyid 데이터 형식을 사용하면 계층적 데이터를 더 쉽게 저장하고 쿼리할 수 있습니다. hierarchyid 는 계층적 데이터의 가장 일반적인 유형인 트리를 표시하는 데 최적화되어 있습니다.

계층적 데이터는 계층적 관계에 의해 서로 관련된 데이터 항목 집합으로 정의됩니다. 계층적 관계는 데이터의 한 항목이 다른 항목의 부모인 위치에 있습니다. 데이터베이스에 일반적으로 저장되는 계층적 데이터는 다음과 같습니다.

  • 조직 구조
  • 파일 시스템
  • 프로젝트의 태스크 집합
  • 언어 용어의 분류
  • 웹 페이지 간 링크의 그래프

계층 구조를 사용하여 테이블을 만들거나 다른 위치에 저장된 데이터의 계층 구조를 설명하는 데이터 형식으로 hierarchyid를 사용합니다. Transact-SQL의 hierarchyid 함수를 사용하여 계층적 데이터를 쿼리하고 관리합니다.

키 속성

hierarchyid 데이터 형식의 값은 트리 계층에서의 위치를 나타냅니다. hierarchyid 값의 속성은 다음과 같습니다.

  • 높은 압축성

    n 개 노드가 포함된 트리에서 노드를 나타내는 데 필요한 평균 비트 수는 평균 fanout(노드의 평균 자식 수)에 따라 달라집니다. 작은 fanout (0-7)의 경우 크기는 약 $6log{A}{n}$ 비트입니다. 여기서 A는 평균 fanout입니다. 평균 fanout 수준이 6인 100,000명으로 구성된 조직 계층의 노드는 약 38비트를 사용합니다. 이는 스토리지를 위해 40비트나 5바이트로 반올림됩니다.

  • 깊이 우선 순서로 비교

    두 개의 hierarchyid 값이 a,b,a < b 경우 a는 깊이 우선 트리 탐색에서 b 앞에 온다는 의미입니다. hierarchyid 데이터 형식의 인덱스에는 깊이 우선 순서가 사용되며 깊이 우선 탐색에서 서로 가까이 있는 노드는 서로 가깝게 저장됩니다. 예를 들어 레코드의 자식은 해당 레코드에 인접하게 저장됩니다.

  • 임의 삽입 및 삭제 지원

    GetDescendant (Database Engine) 메서드를 사용하면 지정한 노드의 오른쪽, 지정한 노드의 왼쪽 또는 두 형제 사이에 형제를 생성할 수 있습니다. 임의 개수의 노드를 계층에서 삽입하거나 삭제할 때 비교 속성이 유지됩니다. 대부분의 삽입 및 삭제 시 압축성 속성이 유지됩니다. 그러나 두 노드 간 삽입 시에는 약간 덜 압축된 표현으로 hierarchyid 값이 생성됩니다.

제한 사항

hierarchyid 데이터 형식에는 다음과 같은 제한 사항이 있습니다.

  • hierarchyid 형식의 열은 자동으로 트리를 나타내지 않습니다. 애플리케이션에 따라 원하는 행 간 관계가 값에 반영되도록 hierarchyid 값이 생성되어 할당됩니다. 일부 애플리케이션에는 다른 테이블에 정의된 계층 구조의 위치를 나타내는 hierarchyid 형식의 열이 있을 수 있습니다.

  • hierarchyid 값을 생성하고 할당하는 동시성을 관리하는 것은 애플리케이션에 달려 있습니다. 애플리케이션에서 고유 키 제약 조건을 사용하거나 자체 논리를 통해 자체적으로 고유성을 적용하지 않는 한 열의 hierarchyid 값에 대한 고유성이 보장되지 않습니다.

  • hierarchyid 값으로 표시되는 계층적 관계는 외래 키 관계처럼 적용되지 않습니다. A에게 자녀 B가 있고, A가 삭제된 후 B에게 존재하지 않는 기록으로 관계를 남기는 계층적 관계를 갖는 것이 가능하고 때로는 적절합니다. 이 동작이 허용되지 않는 경우 애플리케이션은 부모를 삭제하기 전에 하위 항목을 쿼리해야 합니다.

hierarchyid에 대한 대체 방법을 사용하는 경우

계층적 데이터를 나타내는 hierarchyid의 두 가지 대안은 다음과 같습니다.

  • 부모/자식
  • XML

hierarchyid는 일반적으로 이러한 대안보다 우수합니다. 그러나 대안이 더 우수할 가능성이 있는 구체적인 상황은 아래에 자세히 설명되어 있습니다.

부모/자식

부모/자식 방법을 사용하는 경우 각 행에는 부모에 대한 참조가 포함됩니다. 다음 테이블에서는 하나의 부모/자식 관계에서 부모 및 자식 행을 포함하는 데 사용되는 일반적인 테이블을 정의합니다.

USE AdventureWorks2022;
GO

CREATE TABLE ParentChildOrg (
    BusinessEntityID INT PRIMARY KEY,
    ManagerId INT REFERENCES ParentChildOrg(BusinessEntityID),
    EmployeeName NVARCHAR(50)
);
GO

공통 작업에 대한 부모/자식 및 hierarchyid 비교

  • hierarchyid를 사용하면 하위 트리 쿼리가 훨씬 더 빨라집니다.
  • hierarchyid를 사용하면 직계 하위 항목 쿼리가 약간 더 느려집니다.
  • hierarchyid를 사용하면 리프가 아닌 노드 이동 속도가 느려집니다.
  • hierarchyid를 사용할 경우 리프가 아닌 노드 삽입과 리프 노드의 삽입 및 이동의 복잡성은 동일합니다.

다음 경우 부모/자식이 더 우수할 수 있습니다.

  • 키의 크기가 중요합니다. 동일한 수의 노드에 대해 hierarchyid 값은 정수 패밀리(smallint, int, bigint) 값과 같거나 큽니다. hierarchyid에서는 부모/자식 구조를 사용할 때 필요한 공통 테이블 식보다 I/O 및 CPU 복잡성의 효율이 훨씬 증가하므로 이 경우에 한해 부모/자식을 많이 사용합니다.

  • 쿼리는 계층 구조의 섹션에서 거의 쿼리하지 않습니다. 즉, 쿼리가 일반적으로 계층의 단일 지점만 다루는 경우입니다. 이러한 경우 공동 위치는 중요하지 않습니다. 예를 들어 조직 테이블이 개별 직원에 대한 급여를 처리하는 데에만 사용되는 경우 부모/자식이 더 우수합니다.

  • 리프가 아닌 하위 트리는 자주 이동하며 성능은 매우 중요합니다. 계층 구조에서 행의 위치를 변경하는 부모/자식 표현에서 단일 행에 영향을 줍니다. 그러나 hierarchyid 사용 시 한 행의 위치를 변경하면 n 개의 행에 영향을 줍니다. 여기서 n 은 이동하는 하위 트리의 노드 수입니다.

    리프가 아닌 하위 트리가 자주 이동하고 성능이 중요하지만 대부분의 이동이 계층 구조의 잘 정의된 수준에 있는 경우 상위 수준과 하위 수준을 두 계층으로 분할하는 것이 좋습니다. 이렇게 하면 모든 이동이 더 높은 계층의 리프 수준으로 이동합니다. 예를 들어 서비스에서 호스트하는 웹 사이트의 계층 구조를 고려합니다. 사이트에는 계층으로 정렬된 많은 페이지가 있습니다. 호스트된 사이트는 사이트 계층 구조의 다른 위치로 이동할 수 있지만 하위 페이지는 거의 다시 정렬되지 않습니다. 다음을 통해 나타낼 수 있습니다.

    CREATE TABLE HostedSites (
    SiteId HIERARCHYID,
    PageId HIERARCHYID
);
GO

XML

XML 문서는 트리이므로 단일 XML 데이터 형식 인스턴스는 전체 계층 구조를 나타낼 수 있습니다. XML 인덱스가 생성 되면 SQL Server에서 계층 구조의 위치를 나타내기 위해 hierarchyid 값이 내부적으로 사용됩니다.

다음과 같은 경우 XML 데이터 형식을 사용하는 것이 더 우수할 수 있습니다.

  • 전체 계층이 항상 저장되고 검색되는 경우
  • 데이터는 애플리케이션에서 XML 형식으로 사용됩니다.
  • 조건자 검색이 매우 제한되어 있으며 성능이 중요하지 않은 경우

예를 들어 애플리케이션에서 여러 조직을 추적하고 전체 조직 계층을 항상 저장 및 검색하며 단일 조직만 쿼리하지 않는 경우 다음 형식의 테이블이 적합할 수 있습니다.

CREATE TABLE XMLOrg (
    Orgid INT,
    Orgdata XML
);
GO

계층적 데이터에 대한 인덱싱 전략

계층적 데이터를 인덱싱하기 위한 두 가지 전략이 있습니다.

  • 깊이 우선

    깊이 우선 인덱스에서는 하위 트리의 행이 서로 가깝게 저장됩니다. 예를 들어 한 관리자를 통해 보고하는 모든 직원은 해당 관리자의 레코드에 가깝게 저장됩니다.

    깊이 우선 인덱스의 경우 노드 하위 트리의 모든 노드가 공동 배치됩니다. 따라서 "이 폴더 및 해당 하위 폴더에서 모든 파일 찾기"와 같은 하위 트리에 대한 쿼리에 답하는 데에는 깊이 우선 인덱스가 효율적입니다.

  • 너비 우선

    폭 우선 인덱스는 계층의 각 수준에 행을 함께 저장합니다. 예를 들어 같은 관리자에게 직접 보고하는 직원의 레코드는 서로 가깝게 저장됩니다.

    너비 우선 인덱스에서는 노드의 모든 직접 자식이 공동 배치됩니다. 따라서 "이 관리자에게 직접 보고하는 모든 직원 찾기"와 같은 인접한 자식에 대한 쿼리에 답하는 데에는 너비 우선 인덱스가 효율적입니다.

깊이 우선, 너비 우선 또는 둘 다를 포함할지 여부와 클러스터링 키(있는 경우)를 만들 것인지는 위의 쿼리 형식의 상대적 중요도와 SELECT 및 DML 작업의 상대적 중요도에 따라 달라집니다. 인덱싱 방법에 대한 자세한 예는 자습서: hierarchyid 데이터 유형 사용하기를 참조하세요.

인덱스 만들기

GetLevel() 메서드를 사용하여 폭 첫 번째 순서를 만들 수 있습니다. 다음 예제에서는 너비 우선 인덱스와 깊이 우선 인덱스가 모두 생성됩니다.

USE AdventureWorks2022;
GO

CREATE TABLE Organization (
    BusinessEntityID HIERARCHYID,
    OrgLevel AS BusinessEntityID.GetLevel(),
    EmployeeName NVARCHAR(50) NOT NULL
);
GO

CREATE CLUSTERED INDEX Org_Breadth_First
ON Organization (OrgLevel, BusinessEntityID);
GO

CREATE UNIQUE INDEX Org_Depth_First
ON Organization (BusinessEntityID);
GO

예제

이 문서의 Transact-SQL 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

기본 예제

다음 예제는 시작하는 데 도움을 주기 위해 의도적으로 단순화된 것입니다. 먼저 일부 지리 데이터를 저장할 테이블을 만듭니다.

CREATE TABLE BasicDemo (
    [Level] HIERARCHYID NOT NULL,
    Location NVARCHAR(30) NOT NULL,
    LocationType NVARCHAR(9) NULL
);

이제 일부 대륙, 국가/지역, 주 및 도시에 대한 데이터를 삽입합니다.

INSERT BasicDemo
VALUES ('/1/', 'Europe', 'Continent'),
    ('/2/', 'South America', 'Continent'),
    ('/1/1/', 'France', 'Country'),
    ('/1/1/1/', 'Paris', 'City'),
    ('/1/2/1/', 'Madrid', 'City'),
    ('/1/2/', 'Spain', 'Country'),
    ('/3/', 'Antarctica', 'Continent'),
    ('/2/1/', 'Brazil', 'Country'),
    ('/2/1/1/', 'Brasilia', 'City'),
    ('/2/1/2/', 'Bahia', 'State'),
    ('/2/1/2/1/', 'Salvador', 'City'),
    ('/3/1/', 'McMurdo Station', 'City');

데이터를 선택하고 수준 데이터를 이해하기 쉬운 텍스트 값으로 변환하는 열을 추가합니다. 또한 이 쿼리는 hierarchyid 데이터 형식을 기준으로 결과를 정렬합니다.

SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

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

Converted Level  Level     Location         LocationType
---------------  --------  ---------------  ---------------
/1/              0x58      Europe           Continent
/1/1/            0x5AC0    France           Country
/1/1/1/          0x5AD6    Paris            City
/1/2/            0x5B40    Spain            Country
/1/2/1/          0x5B56    Madrid           City
/2/              0x68      South America    Continent
/2/1/            0x6AC0    Brazil           Country
/2/1/1/          0x6AD6    Brasilia         City
/2/1/2/          0x6ADA    Bahia            State
/2/1/2/1/        0x6ADAB0  Salvador         City
/3/              0x78      Antarctica       Continent
/3/1/            0x7AC0    McMurdo Station  City

내부적으로 일관되지 않더라도 계층 구조에는 유효한 구조가 있습니다. 바히아는 유일한 주입니다. 도시 Brasilia의 피어로 계층에 나타납니다. 마찬가지로, 맥머도 역에도 모국가나 지역이 없습니다. 사용자는 이 유형의 계층이 해당 계층에 적합한지 결정해야 합니다.

다른 행을 추가하고 결과를 선택합니다.

INSERT BasicDemo
VALUES ('/1/3/1/', 'Kyoto', 'City'),
    ('/1/3/1/', 'London', 'City');

SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

이것은 더 많은 가능한 문제를 보여줍니다. 교토는 /1/3/1/ 부모 수준이 없어도 /1/3/ 수준으로 삽입할 수 있습니다. 그리고 London과 Kyoto의 hierarchyid값은 동일합니다. 다시, 사용자는 이 유형의 계층 구조가 해당 용도에 적합한지 결정하고 사용법이 잘못된 값을 차단해야 합니다.

또한 이 테이블에서는 계층의 최상위( '/')를 사용하지 않았습니다. 모든 대륙의 공통 부모가 없기 때문에 생략되었습니다. 행성 전체를 추가하여 추가할 수 있습니다.

INSERT BasicDemo
VALUES ('/', 'Earth', 'Planet');

부모/자식에서 hierarchyid로 마이그레이션

대부분의 트리는 부모/자식으로 표시됩니다. hierarchyid를 사용하여 부모/자식 구조에서 테이블로 마이그레이션하는 가장 쉬운 방법은 임시 열 또는 임시 테이블을 사용하여 계층의 각 수준에서 노드 수를 추적하는 것입니다. 부모/자식 테이블을 마이그레이션하는 예제는 자습서: hierarchyid 데이터 형식 사용하기의 1 단원을 참조하세요.

hierarchyid를 사용하여 트리 관리

hierarchyid 열이 반드시 트리를 나타내는 것은 아니지만 애플리케이션에서 손쉽게 해당 열이 트리를 나타내도록 만들 수 있습니다.

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

    • 부모 행의 마지막 자식 번호를 추적합니다.
    • 마지막 자식을 컴퓨팅합니다. 이 작업을 효율적으로 수행하려면 폭 우선 인덱스가 필요합니다.

  • 클러스터링 키의 일부로 열에 고유한 인덱스 만들기를 통해 고유성을 적용합니다. 고유한 값이 삽입되도록 하려면 다음 중 하나를 수행합니다.

    • 고유 키 위반 오류를 검색하고 다시 시도합니다.
    • 각 새 자식 노드의 고유성을 확인하고 직렬화 가능한 트랜잭션의 일부로 삽입합니다.

오류 검색을 사용하는 예제

다음 예제에서 샘플 코드는 새 자식 EmployeeId 값을 계산한 다음 키 위반을 감지하고 INS_EMP 표식으로 반환하여 새 행에 대한 EmployeeId 값을 다시 계산합니다.

USE AdventureWorks;
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 INTO 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;

    SELECT @last_child = EmployeeId.GetDescendant(LastChild, NULL)
    FROM Org_T2
    WHERE EmployeeId = @mgrid;

    UPDATE Org_T2
    SET LastChild = @last_child
    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; // SqlFunction Attribute
using Microsoft.SqlServer.Types;  // SqlHierarchyId

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.IsDescendantOf(h2))
        {
            h1 = h1.GetAncestor(1);
        }

        return h1;
    }
}

다음 Transact-SQL 예제에서 ListAncestorL과 CommonAncestor 메서드를 사용하려면 다음과 유사한 코드를 실행하여 DLL을 빌드하고 SQL Server에서 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
INNER 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

관련 콘텐츠