1단원: 테이블을 계층 구조로 변환

아티클
03/22/2024

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

자체 조인을 사용하여 계층적 관계를 표현하는 테이블이 있는 고객은 이 단원을 가이드로 사용하여 테이블을 계층 구조로 변환할 수 있습니다. 이 표현에서 hierarchyid를 사용하는 표현으로 마이그레이션하는 비교적 쉽습니다. 마이그레이션하면 사용자는 효율적인 쿼리를 위해 여러 가지 방법으로 인덱싱할 수 있는 간결하고 이해하기 쉬운 계층적 표현을 갖게 됩니다.

이 단원에서는 기존 테이블을 검사하고, hierarchyid 열을 포함하는 새 테이블을 만들고, 테이블을 원본 테이블의 데이터로 채운 다음, 세 가지 인덱싱 전략을 보여줍니다. 이 문서에 포함된 주제는 다음과 같습니다.

필수 조건

이 자습서를 완료하려면 SQL Server Management Studio, SQL Server를 실행하는 서버에 대한 액세스 및 AdventureWorks 데이터베이스가 필요합니다.

SQL Server Management Studio를 설치합니다.
SQL Server 2017 Developer Edition을 설치합니다.
AdventureWorks 샘플 데이터베이스를 다운로드합니다.

SSMS에서 데이터베이스를 복원하기 위한 지침은 여기: 데이터베이스 복원에 있습니다.

Employee 테이블의 현재 구조 검사

AdventureWorks2022 샘플 데이터베이스에는 Employee 테이블이 HumanResources 스키마에 포함되어 있습니다. 원래 테이블이 변경되지 않도록 이 단계에서는 EmployeeDemo 라는 Employee테이블의 복사본을 만듭니다. 예를 단순화하기 위해 원래 테이블에서 5개의 열만 복사합니다. 그런 다음, HumanResources.EmployeeDemo 테이블을 쿼리하여 hierarchyid 데이터 형식을 사용하지 않고 데이터가 테이블에서 구조화되어 있는 방법을 검토합니다.

Employee 테이블 복사

쿼리 편집기 창에서 다음 코드를 실행하여 테이블 구조와 데이터를 Employee 테이블에서 EmployeeDemo라는 새 테이블로 복사합니다. 원래 테이블은 이미 hierarchyid를 사용하고 있으므로 이 쿼리는 기본적으로 계층 구조를 평면화하여 직원의 관리자를 검색합니다. 이 단원의 다음 부분에서는 이 계층 구조를 다시 구성합니다.

USE AdventureWorks2022;  
GO  
  if OBJECT_ID('HumanResources.EmployeeDemo') is not null
 drop table HumanResources.EmployeeDemo 

 SELECT emp.BusinessEntityID AS EmployeeID, emp.LoginID, 
  (SELECT  man.BusinessEntityID FROM HumanResources.Employee man 
	    WHERE emp.OrganizationNode.GetAncestor(1)=man.OrganizationNode OR 
		    (emp.OrganizationNode.GetAncestor(1) = 0x AND man.OrganizationNode IS NULL)) AS ManagerID,
       emp.JobTitle, emp.HireDate
INTO HumanResources.EmployeeDemo   
FROM HumanResources.Employee emp ;
GO

EmployeeDemo 테이블의 구조와 데이터 검사

이 새 EmployeeDemo 테이블은 새 구조로 마이그레이션할 수 있는 기존 데이터베이스의 일반적인 테이블을 나타냅니다. 쿼리 편집기 창에서 다음 코드를 실행하여 테이블에서 자체 조인을 사용하여 직원/관리자 관계를 표시하는 방법을 보여줍니다.

SELECT   
    Mgr.EmployeeID AS MgrID, Mgr.LoginID AS Manager,   
    Emp.EmployeeID AS E_ID, Emp.LoginID, Emp.JobTitle  
FROM HumanResources.EmployeeDemo AS Emp  
LEFT JOIN HumanResources.EmployeeDemo AS Mgr  
ON Emp.ManagerID = Mgr.EmployeeID  
ORDER BY MgrID, E_ID

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

MgrID Manager                 E_ID LoginID                  JobTitle  
NULL	NULL	                1	adventure-works\ken0	    Chief Executive Officer
1	adventure-works\ken0	    2	adventure-works\terri0   	Vice President of Engineering
1	adventure-works\ken0	   16	adventure-works\david0	  Marketing Manager
1	adventure-works\ken0	   25	adventure-works\james1	  Vice President of Production
1	adventure-works\ken0	  234	adventure-works\laura1	  Chief Financial Officer
1	adventure-works\ken0  	263	adventure-works\jean0	    Information Services Manager
1	adventure-works\ken0	  273	adventure-works\brian3	  Vice President of Sales
2	adventure-works\terri0	  3	adventure-works\roberto0	Engineering Manager
3	adventure-works\roberto0	4	adventure-works\rob0	    Senior Tool Designer
...

결과는 총 290개 행에 대해 계속됩니다.

ORDER BY 절에 따라 출력에 각 관리 수준의 부하 직원이 모두 나열됩니다. 예를 들어 1 MgrID(ken0)의 7명 부하 직원이 모두 서로 인접하여 나열됩니다. 불가능하지는 않지만 최종적으로 1 MgrID에 보고하는 모든 직원을 그룹화하는 것이 훨씬 더 어렵습니다.

기존 계층적 데이터로 테이블 채우기

이 태스크에서는 새 테이블을 만들고 이를 EmployeeDemo 테이블의 데이터로 채웁니다. 이 태스크의 단계는 다음과 같습니다.

hierarchyid 열이 포함된 새 테이블을 만듭니다. 이 열은 기존 EmployeeID 및 ManagerID 열을 바꿀 수 있습니다. 그러나 이러한 열은 유지됩니다. 이는 기존 애플리케이션에서 해당 열을 참조하고 전송 후 데이터를 이해하는 데 도움이 될 수 있기 때문입니다. 테이블 정의는 OrgNode가 고유한 값을 열에 포함해야 하는 기본 키임을 지정합니다. OrgNode 열의 클러스터형 인덱스는 OrgNode 시퀀스의 날짜를 저장합니다.
각 관리자에게 직접 보고하는 직원의 수를 추적하는 데 사용되는 임시 테이블을 만듭니다.
EmployeeDemo 테이블의 데이터를 사용하여 새 테이블을 채웁다.

NewOrg라는 새 테이블 만들기

쿼리 편집기 창에서 다음 코드를 실행하여 HumanResources.NewOrg라는 새 테이블을 만듭니다.

CREATE TABLE HumanResources.NewOrg  
(  
  OrgNode hierarchyid,  
  EmployeeID int,  
  LoginID nvarchar(50),  
  ManagerID int  
CONSTRAINT PK_NewOrg_OrgNode  
  PRIMARY KEY CLUSTERED (OrgNode)  
);  
GO

#Children이라는 임시 테이블 만들기

각 노드에 대한 자식 수를 포함하는 Num이라는 열을 사용하여 #Children이라는 임시 테이블을 만듭니다.
```
CREATE TABLE #Children   
   (  
    EmployeeID int,  
    ManagerID int,  
    Num int  
);  
GO  
```
NewOrg 테이블을 채우는 쿼리의 속도를 크게 높일 수 있는 인덱스를 추가합니다.
```
CREATE CLUSTERED INDEX tmpind ON #Children(ManagerID, EmployeeID);  
GO  
```

NewOrg 테이블 채우기

재귀 쿼리는 집계가 있는 하위 쿼리를 금지합니다. 대신 ROW_NUMBER() 메서드를 사용하여 Num 열을 채우는 다음 코드를 사용하여 #Children 테이블을 채웁니다.
```
INSERT #Children (EmployeeID, ManagerID, Num)  
SELECT EmployeeID, ManagerID,  
  ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID)   
FROM HumanResources.EmployeeDemo  
GO 
```

#Children 테이블을 검토합니다. 각 관리자에 대한 일련 번호가 Num 열에 포함되는 방법을 확인합니다.

SELECT * FROM #Children ORDER BY ManagerID, Num  
GO

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

EmployeeID	ManagerID	Num
1  	NULL  	1
2	     1    1
16	   1	  2
25	   1	  3
234	   1	  4
263	   1	  5
273	   1	  6
3	     2	  1
4	     3	  1
5	     3	  2
6	     3	  3
7	     3	  4

NewOrg 테이블 채우기 GetRoot 및 ToString 메서드를 사용하여 Num 값을 hierarchyid 형식에 연결한 다음 OrgNode 열을 결과 계층 값으로 업데이트합니다.

WITH paths(path, EmployeeID)   
AS (  
-- This section provides the value for the root of the hierarchy  
SELECT hierarchyid::GetRoot() AS OrgNode, EmployeeID   
FROM #Children AS C   
WHERE ManagerID IS NULL   

UNION ALL   
-- This section provides values for all nodes except the root  
SELECT   
CAST(p.path.ToString() + CAST(C.Num AS varchar(30)) + '/' AS hierarchyid),   
C.EmployeeID  
FROM #Children AS C   
JOIN paths AS p   
   ON C.ManagerID = P.EmployeeID   
)  
INSERT HumanResources.NewOrg (OrgNode, O.EmployeeID, O.LoginID, O.ManagerID)  
SELECT P.path, O.EmployeeID, O.LoginID, O.ManagerID  
FROM HumanResources.EmployeeDemo AS O   
JOIN Paths AS P   
   ON O.EmployeeID = P.EmployeeID  
GO

hierarchyid 열은 문자 형식으로 변환하면 더 쉽게 이해할 수 있습니다. OrgNode 열의 두 가지 표현이 포함된 다음 코드를 실행하여 NewOrg 테이블의 데이터를 검토합니다.
```
SELECT OrgNode.ToString() AS LogicalNode, *   
FROM HumanResources.NewOrg   
ORDER BY LogicalNode;  
GO  
```
LogicalNode 열은 계층 구조를 나타내는 더 읽기 쉬운 텍스트 형식의 hierarchyid 열을 변환합니다. 나머지 작업에서는 ToString() 메서드를 사용하여 hierarchyid 열의 논리적 형식을 표시합니다.
더 이상 필요하지 않은 임시 테이블을 삭제합니다.
```
DROP TABLE #Children  
GO  
```

NewOrg 테이블 최적화

기존 계층적 데이터로 테이블 채우기 작업에서 만든 NewOrd 테이블에는 모든 직원 정보가 포함되어 있으며, hierarchyid 데이터 형식을 사용하여 계층 구조를 나타냅니다. 이 작업은 hierarchyid 열에 대한 검색을 지원할 수 있는 새 인덱스를 추가합니다.

hierarchyid 열(OrgNode)은 NewOrg 테이블의 기본 키입니다. 테이블을 만들었을 때 OrgNode 열의 고유성을 적용하기 위해 PK_NewOrg_OrgNode이라는 클러스터형 인덱스가 포함되었습니다. 이 클러스터형 인덱스는 테이블의 깊이 우선 검색도 지원합니다.

NewOrg 테이블에 효율적인 검색을 위한 인덱스 만들기

계층 구조에서 동일한 수준의 쿼리를 지원하려면 GetLevel 메서드를 사용하여 계층의 수준을 포함하는 계산 열을 만듭니다. 그런 다음, 복합 인덱스를 해당 수준과 Hierarchyid에 만듭니다. 다음 코드를 실행하여 계산 열 및 너비 우선 인덱스를 만듭니다.
```
ALTER TABLE HumanResources.NewOrg   
   ADD H_Level AS OrgNode.GetLevel() ;  
CREATE UNIQUE INDEX EmpBFInd   
   ON HumanResources.NewOrg(H_Level, OrgNode) ;  
GO  
```
고유 인덱스를 EmployeeID 열에 만듭니다. 이는 EmployeeID 번호를 기준으로 단일 직원을 단일 조회하는 일반적인 방법입니다. 다음 코드를 실행하여 인덱스를 EmployeeID 열에 만듭니다.
```
CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;  
GO
```

다음 코드를 실행하여 테이블에서 데이터를 세 가지 인덱스 각각의 순서대로검색합니다.

SELECT OrgNode.ToString() AS LogicalNode,  
OrgNode, H_Level, EmployeeID, LoginID  
FROM HumanResources.NewOrg   
ORDER BY OrgNode;  

SELECT OrgNode.ToString() AS LogicalNode,  
OrgNode, H_Level, EmployeeID, LoginID   
FROM HumanResources.NewOrg   
ORDER BY H_Level, OrgNode;  

SELECT OrgNode.ToString() AS LogicalNode,  
OrgNode, H_Level, EmployeeID, LoginID   
FROM HumanResources.NewOrg   
ORDER BY EmployeeID;  
GO

결과 집합을 비교하여 순서가 각 인덱스 형식에 저장되는 방법을 확인합니다. 각 출력의 처음 4개 행만 표시됩니다.

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

깊이 우선 인덱스: Employee 레코드가 관리자와 인접하여 저장됩니다.

LogicalNode	OrgNode	H_Level	EmployeeID	LoginID
/	0x	0	1	adventure-works\ken0
/1/	0x58	1	2	adventure-works\terri0
/1/1/	0x5AC0	2	3	adventure-works\roberto0
/1/1/1/	0x5AD6	3	4	adventure-works\rob0
/1/1/2/	0x5ADA	3	5	adventure-works\gail0
/1/1/3/	0x5ADE	3	6	adventure-works\jossef0
/1/1/4/	0x5AE1	3	7	adventure-works\dylan0
/1/1/4/1/	0x5AE158	4	8	adventure-works\diane1
/1/1/4/2/	0x5AE168	4	9	adventure-works\gigi0
/1/1/4/3/	0x5AE178	4	10	adventure-works\michael6
/1/1/5/	0x5AE3	3	11	adventure-works\ovidiu0

EmployeeID 우선 인덱스: 행이 EmployeeID 시퀀스에 저장됩니다.

LogicalNode	OrgNode	H_Level	EmployeeID	LoginID
/	0x	0	1	adventure-works\ken0
/1/	0x58	1	2	adventure-works\terri0
/1/1/	0x5AC0	2	3	adventure-works\roberto0
/1/1/1/	0x5AD6	3	4	adventure-works\rob0
/1/1/2/	0x5ADA	3	5	adventure-works\gail0
/1/1/3/	0x5ADE	3	6	adventure-works\jossef0
/1/1/4/	0x5AE1	3	7	adventure-works\dylan0
/1/1/4/1/	0x5AE158	4	8	adventure-works\diane1
/1/1/4/2/	0x5AE168	4	9	adventure-works\gigi0
/1/1/4/3/	0x5AE178	4	10	adventure-works\michael6
/1/1/5/	0x5AE3	3	11	adventure-works\ovidiu0
/1/1/5/1/	0x5AE358	4	12	adventure-works\thierry0

참고

깊이 우선 인덱스와 너비 우선 인덱스의 차이를 보여 주는 다이어그램은 계층적 데이터(SQL Server)를 참조하세요.

불필요한 열 삭제

ManagerID 열은 직원/관리자 관계를 나타내며, 이제 OrgNode 열로 표시됩니다. 다른 애플리케이션에 ManagerID 열이 필요하지 않은 경우 다음 문을 사용하여 삭제하는 것이 좋습니다.
```
ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;  
GO  
```
EmployeeID 열도 중복됩니다. OrgNode 열은 각 직원을 고유하게 식별합니다. 다른 애플리케이션에 EmployeeID 열이 필요하지 않은 경우 다음 코드를 사용하여 인덱스와 열을 차례로 삭제하는 것이 좋습니다.
```
DROP INDEX EmpIDs_unq ON HumanResources.NewOrg ;  
ALTER TABLE HumanResources.NewOrg DROP COLUMN EmployeeID ;  
GO  
```

원래 테이블을 새 테이블로 바꾸기

추가 인덱스 또는 제약 조건이 원래 테이블에 포함된 경우 이러한 항목을 NewOrg 테이블에 추가합니다.
이전 EmployeeDemo 테이블을 새 테이블로 바꿉니다. 다음 코드를 실행하여 이전 테이블을 삭제한 다음, 새 테이블의 이름을 이전 이름으로 바꿉니다.
```
DROP TABLE HumanResources.EmployeeDemo ;  
GO  
sp_rename 'HumanResources.NewOrg', 'EmployeeDemo' ;  
GO  
```
다음 코드를 실행하여 최종 테이블을 검사합니다.
```
SELECT * FROM HumanResources.EmployeeDemo ;  
```

다음 단계

다음 문서에서는 계층적 테이블에서 데이터를 만들고 관리하는 방법을 설명합니다.

자세히 알아보려면 다음 문서로 이동하세요.

다음 단계

다음을 통해 공유

1단원: 테이블을 계층 구조로 변환

필수 조건

Employee 테이블의 현재 구조 검사

Employee 테이블 복사

EmployeeDemo 테이블의 구조와 데이터 검사

기존 계층적 데이터로 테이블 채우기

NewOrg라는 새 테이블 만들기

#Children이라는 임시 테이블 만들기

NewOrg 테이블 채우기

NewOrg 테이블 최적화

NewOrg 테이블에 효율적인 검색을 위한 인덱스 만들기

불필요한 열 삭제

원래 테이블을 새 테이블로 바꾸기

다음 단계

피드백

추가 리소스