[T-SQL] T-SQL New Features : HierarchyID


SQL Server 2008에서는 계층 구조형 데이터를 지원하기 위한 HierarchyID 데이터 타입을 지원합니다.
HierarchyID
데이터 타입을 이용하면 기존 SQL Server 2000 재귀 쿼리 2005 CTE (공통 테이블 ) 이용하는 보다 훨씬 편하게 조직도 등의 계층 구조를 표현할 있습니다. (해당 내용은 BOL에서도 확인하실 있습니다.)
 – GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendantOf 
다양한 메소드를 통해 쉽게 계층구조 쿼리를 있습니다.
 –
깊이 우선 인덱스 (Depth-first Index) 너비 우선 인덱스 (Breadth-First Index) 지원하여 쿼리 성능을 보장합니다.

그럼 간단하게 HierarchyID를 사용하여 계층구조를 표현해 보도록 하겠습니다.
전체적인 조직도는 아래와 같이 출력해보려 합니다.

/************************************
David (Marketing Manager)

 └ Sariya (Marketing Specialist)
    └  Wanida (Marketing Assistant)

 └ John (Marketing Specialist)
    └ Mary (Marketing Assistant)

 └ Jill (Marketing Specialist)
************************************/

 

우선 아래와 같이 테스트를 위한 샘플 테이블 및 인덱스를 생성합니다.

— HierarchyID 데이터 타입을 사용하는 테이블 작성
CREATE TABLE HumanResources.EmployeeOrg
(
   OrgNode hierarchyid PRIMARY KEY CLUSTERED,
   EmployeeID int UNIQUE NOT NULL,
   EmpName varchar(20) NOT NULL,
   Title varchar(20) NULL
) ;
GO

깊이 우선 인덱스 생성 (depth-first index)
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgNode) ;
GO

 

이제 테스트를 위한 샘플 데이터를 테이블에 넣으려 하는데요, Root 데이터를 INSERT하고 나머지 데이터는 프로시저를 만들어서 넣도록 하겠습니다.

— Root 데이터(David, Marketing Manager) 추가
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, ‘David’, ‘Marketing Manager’) ;
GO

직원을 추가하기 위한 프로시저 생성 (파라미터 : Manager ID, 직원 ID, 이름, 직책)
CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20))
AS
BEGIN
   DECLARE @mOrgNode hierarchyid, @lc hierarchyid
   SELECT @mOrgNode = OrgNode
   FROM HumanResources.EmployeeOrg
   WHERE EmployeeID = @mgrid
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRANSACTION
     
SELECT @lc = max(OrgNode)
      FROM HumanResources.EmployeeOrg
      WHERE OrgNode.GetAncestor(1)
=@mOrgNode ;

      INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title)

   COMMIT
END ;
GO

프로시저를 이용하여 David Manager(Parent Node)로 하는 직원 추가
EXEC AddEmp 6, 46, ‘Sariya’, ‘Marketing Specialist’ ;
EXEC AddEmp 6, 271, ‘John’, ‘Marketing Specialist’ ;
EXEC AddEmp 6, 119, ‘Jill’, ‘Marketing Specialist’ ;

프로시저를 이용하여 Sariya Manager(Parent Node)로 하는 직원 추가
EXEC AddEmp 46, 269, ‘Wanida’, ‘Marketing Assistant’ ;

프로시저를 이용하여 John Manager(Parent Node)로 하는 직원 추가
EXEC AddEmp 271, 272, ‘Mary’, ‘Marketing Assistant’

 

데이터를 넣은 SELECT 문을 이용해 전체 계층 구조를 쿼리해 보면 아래와 같이 결과를 얻을 있습니다.

전체 조직도 출력
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, EmployeeID, EmpName, Title
FROM dbo.EmployeeOrg ;

 

만약 조직도 내에서 직원의 조직 레벨을 출력하고 싶을 경우엔 GetLevel() 함수를 사용하여 쉽게 출력 가능합니다.

각 직원별 조직도에서의 레벨을 포함하여 출력
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM dbo.EmployeeOrg ;
GO

 

특정 직원의 부모 노드를 Root노드 까지 모두 출력해 보고 싶을 경우엔 IsDescendantOf() 함수를 이용할 있습니다.

특정 직원의 부모 노드(Root까지)를 모두 출력
DECLARE @CurrentEmployee hierarchyid

SELECT @CurrentEmployee = OrgNode
FROM dbo.EmployeeOrg
WHERE EmployeeID = 269 ;

SELECT *
FROM dbo.EmployeeOrg
WHERE @CurrentEmployee.IsDescendantOf(OrgNode) = 1 ;

이렇듯 HierarchyID 데이터 타입을 이용하면, 기존에 재귀 처리 등을 통해 구현하여야 했던 계층 구조를 손쉽게 구현하는 것이 가능합니다.

 

Comments (0)