[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 데이터 타입을 이용하면, 기존에 재귀 처리 등을 통해 구현하여야 했던 계층 구조를 손쉽게 구현하는 것이 가능합니다.