DataBaser.Net: Hierarchyid Data Type

Hierarchyid는 다음과 같은 특징과 이점을 가진다.

계층적 작업을 위한 Data type
- 조직구조
- 파일 시스템
- 프로젝트 태스트 집합
- 언어 용어의 분류
- 웹 페이지 간 링크의 그래프
높은 압축성

USE AdventureWorks
GO

--테이블생성
CREATE TABLE HumanResources.EmployeeOrg
(
   OrgNode hierarchyid PRIMARY KEY CLUSTERED, --여기주목!
   OrgLevel AS OrgNode.GetLevel(), --여기주목!
   EmployeeID int UNIQUE NOT NULL,
   EmpName varchar(20) NOT NULL,
   Title varchar(20) NULL
) ;
GO

--인덱스생성(넓이기반인덱스생성)
CREATE UNIQUE INDEX EmployeeOrgNc1 
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;
GO

--루트삽입
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ;
GO

--검색1

SELECT * FROM HumanResources.EmployeeOrg

--검색2
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg

부하직원 삽입(GetDescendant()메서드사용)

DECLARE @Manager hierarchyid 
SELECT @Manager = hierarchyid::GetRoot()
FROM HumanResources.EmployeeOrg ;

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES
(@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist') ; 

--검색
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg

--귀찮으니프로시저생성후삽입
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

EXEC AddEmp 6, 271, 'John', 'Marketing Specialist' ;
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist' ;
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant' ;
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant' ;
EXEC AddEmp 6, 120, 'yasi', 'Marketing Specialist' ;

--검색
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg

SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	hierarchyid::GetRoot() AS Text_Root
,	OrgNode.GetAncestor(1) AS Text_Ancestor
,	(SELECT EmpName FROM HumanResources.EmployeeOrg B
	WHERE B.OrgNode = A.OrgNode.GetDescendant(0x78, NULL)) 자식
FROM HumanResources.EmployeeOrg A
WHERE EmpName = 'David'