Pelajaran 1: Mengonversi Tabel menjadi Struktur Hierarkis

Artikel
04/26/2023

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Pelanggan yang memiliki tabel yang menggunakan gabungan mandiri untuk mengekspresikan hubungan hierarkis dapat mengonversi tabel mereka menjadi struktur hierarkis menggunakan pelajaran ini sebagai panduan. Relatif mudah untuk bermigrasi dari representasi ini ke representasi ini menggunakan hierarkiid. Setelah migrasi, pengguna akan memiliki representasi hierarkis yang ringkas dan mudah dipahami, yang dapat diindeks dalam beberapa cara untuk kueri yang efisien.

Pelajaran ini, memeriksa tabel yang ada, membuat tabel baru yang berisi kolom hierarkiid , mengisi tabel dengan data dari tabel sumber, lalu menunjukkan tiga strategi pengindeksan. Pelajaran ini berisi topik-topik berikut:

Prasyarat

Untuk menyelesaikan tutorial ini, Anda memerlukan SQL Server Management Studio, akses ke server yang menjalankan SQL Server, dan database AdventureWorks.

Instal SQL Server Management Studio.
Instal Edisi Pengembang SQL Server 2017.
Unduh database sampel AdventureWorks.

Instruksi untuk memulihkan database di SQL Server Management Directory ada di sini: Memulihkan database.

Memeriksa struktur tabel karyawan saat ini

Database sampel AdventureWorks2022 berisi tabel Karyawan dalam skema HumanResources . Untuk menghindari perubahan tabel asli, langkah ini membuat salinan tabel Karyawan bernama EmployeeDemo. Untuk menyederhanakan contoh, Anda hanya menyalin lima kolom dari tabel asli. Kemudian, Anda mengkueri tabel HumanResources.EmployeeDemo untuk meninjau bagaimana data disusun dalam tabel tanpa menggunakan jenis data hierarki.

Salin tabel Karyawan

Di jendela Editor Kueri, jalankan kode berikut untuk menyalin struktur tabel dan data dari tabel Karyawan ke dalam tabel baru bernama EmployeeDemo. Karena tabel asli sudah menggunakan hierarkiid, kueri ini pada dasarnya meratakan hierarki untuk mengambil manajer karyawan. Di bagian berikutnya dari pelajaran ini kita akan merekonstruksi hierarki ini.

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

Memeriksa struktur dan data tabel EmployeeDemo

Tabel EmployeeDemo baru ini mewakili tabel umum dalam database yang sudah ada yang mungkin ingin Anda migrasikan ke struktur baru. Di jendela Editor Kueri, jalankan kode berikut untuk memperlihatkan bagaimana tabel menggunakan gabungan mandiri untuk menampilkan hubungan karyawan/manajer:

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

Berikut adalah hasil yang ditetapkan.

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
...

Hasilnya berlanjut untuk total 290 baris.

Perhatikan bahwa klausul ORDER BY menyebabkan output mencantumkan laporan langsung dari setiap tingkat manajemen bersama-sama. Misalnya, ketujuh laporan langsung MgrID 1 (ken0) tercantum berdekatan satu sama lain. Meskipun bukan tidak mungkin, jauh lebih sulit untuk mengelompokkan semua orang yang akhirnya melapor ke MgrID 1.

Mengisi Tabel dengan Data Hierarkis yang Ada

Tugas ini membuat tabel baru dan mengisinya dengan data dalam tabel EmployeeDemo . Tugas ini memiliki langkah-langkah berikut:

Buat tabel baru yang berisi kolom hierarkiid . Kolom ini dapat menggantikan kolom EmployeeID dan ManagerID yang ada. Namun, Anda akan mempertahankan kolom tersebut. Ini karena aplikasi yang ada mungkin merujuk ke kolom tersebut, dan juga untuk membantu Anda memahami data setelah transfer. Definisi tabel menentukan bahwa OrgNode adalah kunci utama, yang mengharuskan kolom berisi nilai unik. Indeks berkluster pada kolom OrgNode akan menyimpan tanggal dalam urutan OrgNode .
Buat tabel sementara yang digunakan untuk melacak berapa banyak karyawan yang melapor langsung ke setiap manajer.
Isi tabel baru dengan menggunakan data dari tabel EmployeeDemo .

Untuk membuat tabel baru bernama NewOrg

Di jendela Editor Kueri, jalankan kode berikut untuk membuat tabel baru bernama HumanResources.NewOrg:

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

Membuat tabel sementara bernama #Children

Buat tabel sementara bernama #Children dengan kolom bernama Num yang akan berisi jumlah anak untuk setiap simpul:
```
CREATE TABLE #Children   
   (  
    EmployeeID int,  
    ManagerID int,  
    Num int  
);  
GO  
```
Tambahkan indeks yang akan mempercepat kueri yang mengisi tabel NewOrg secara signifikan:
```
CREATE CLUSTERED INDEX tmpind ON #Children(ManagerID, EmployeeID);  
GO  
```

Mengisi tabel NewOrg

Kueri rekursif melarang subkueri dengan agregat. Sebagai gantinya , isi tabel #Children dengan kode berikut, yang menggunakan metode ROW_NUMBER() untuk mengisi kolom Num :

INSERT #Children (EmployeeID, ManagerID, Num)  
SELECT EmployeeID, ManagerID,  
  ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID)   
FROM HumanResources.EmployeeDemo  
GO

Tinjau tabel #Children. Perhatikan bagaimana kolom Num berisi nomor berurutan untuk setiap manajer.

SELECT * FROM #Children ORDER BY ManagerID, Num  
GO

Berikut adalah hasil yang ditetapkan.

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

Isi tabel NewOrg . Gunakan metode GetRoot dan ToString untuk menggabungkan nilai Num ke dalam format hierarkiid , lalu perbarui kolom OrgNode dengan nilai hierarkis yang dihasilkan:

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

Kolom hierarki lebih dapat dimengerti saat Anda mengonversinya menjadi format karakter. Tinjau data dalam tabel NewOrg dengan menjalankan kode berikut, yang berisi dua representasi kolom OrgNode :
```
SELECT OrgNode.ToString() AS LogicalNode, *   
FROM HumanResources.NewOrg   
ORDER BY LogicalNode;  
GO  
```
Kolom LogicalNode mengonversi kolom hierarkiid menjadi formulir teks yang lebih dapat dibaca yang mewakili hierarki. Di tugas yang tersisa, Anda akan menggunakan ToString() metode untuk memperlihatkan format logis kolom hierarkis .
Jatuhkan tabel sementara, yang tidak lagi diperlukan:
```
DROP TABLE #Children  
GO  
```

Mengoptimalkan Tabel NewOrg

Tabel NewOrd yang Anda buat di tugas Mengisi Tabel dengan Data Hierarkis yang Ada berisi semua informasi karyawan, dan mewakili struktur hierarkis dengan menggunakan tipe data hierarkis . Tugas ini menambahkan indeks baru untuk mendukung pencarian pada kolom hierarkisid .

Kolom hierarkiid (OrgNode) adalah kunci utama untuk tabel NewOrg. Ketika tabel dibuat, tabel berisi indeks berkluster bernama PK_NewOrg_OrgNode untuk memberlakukan keunikan kolom OrgNode . Indeks berkluster ini juga mendukung pencarian tabel yang mengutamakan kedalaman.

Membuat indeks pada tabel NewOrg untuk pencarian yang efisien

Untuk membantu kueri pada tingkat yang sama dalam hierarki, gunakan metode GetLevel untuk membuat kolom komputasi yang berisi tingkat dalam hierarki. Kemudian, buat indeks komposit pada tingkat dan Hierarkiid. Jalankan kode berikut untuk membuat kolom komputasi dan indeks yang mengutamakan luas:
```
ALTER TABLE HumanResources.NewOrg   
   ADD H_Level AS OrgNode.GetLevel() ;  
CREATE UNIQUE INDEX EmpBFInd   
   ON HumanResources.NewOrg(H_Level, OrgNode) ;  
GO  
```
Buat indeks unik pada kolom EmployeeID . Ini adalah pencarian singleton tradisional dari satu karyawan dengan nomor EmployeeID . Jalankan kode berikut untuk membuat indeks di EmployeeID:
```
CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;  
GO
```

Jalankan kode berikut untuk mengambil data dari tabel dalam urutan masing-masing dari tiga indeks:

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

Bandingkan kumpulan hasil untuk melihat bagaimana urutan disimpan di setiap jenis indeks. Hanya empat baris pertama dari setiap output yang mengikuti.

Berikut adalah hasil yang ditetapkan.

Indeks yang mengutamakan kedalaman: Catatan karyawan disimpan berdekatan dengan manajer mereka.

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

Indeks employeeID-first: Baris disimpan dalam urutan 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

Catatan

Untuk diagram yang memperlihatkan perbedaan antara indeks mengutamakan kedalaman dan indeks yang mengutamakan luas, lihat Data Hierarkis (SQL Server).

Menghapus kolom yang tidak perlu

Kolom ManagerID mewakili hubungan karyawan/manajer, yang sekarang diwakili oleh kolom OrgNode . Jika aplikasi lain tidak memerlukan kolom ManagerID , pertimbangkan untuk menghilangkannya dengan menggunakan pernyataan berikut:
```
ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;  
GO  
```
Kolom EmployeeID juga berlebihan. Kolom OrgNode secara unik mengidentifikasi setiap karyawan. Jika aplikasi lain tidak memerlukan kolom EmployeeID , pertimbangkan untuk menghilangkan indeks lalu kolom dengan menggunakan kode berikut:
```
DROP INDEX EmpIDs_unq ON HumanResources.NewOrg ;  
ALTER TABLE HumanResources.NewOrg DROP COLUMN EmployeeID ;  
GO  
```

Ganti tabel asli dengan tabel baru

Jika tabel asli Anda berisi indeks atau batasan tambahan, tambahkan ke tabel NewOrg .
Ganti tabel EmployeeDemo lama dengan tabel baru. Jalankan kode berikut untuk menghilangkan tabel lama, lalu ganti nama tabel baru dengan nama lama:
```
DROP TABLE HumanResources.EmployeeDemo ;  
GO  
sp_rename 'HumanResources.NewOrg', 'EmployeeDemo' ;  
GO  
```
Jalankan kode berikut untuk memeriksa tabel akhir:
```
SELECT * FROM HumanResources.EmployeeDemo ;  
```

Langkah berikutnya

Artikel berikutnya mengajarkan Anda untuk membuat dan mengelola data dalam tabel hierarkis.

Buka artikel berikutnya untuk mempelajari selengkapnya:

Langkah berikutnya

Bagikan melalui

Pelajaran 1: Mengonversi Tabel menjadi Struktur Hierarkis

Prasyarat

Memeriksa struktur tabel karyawan saat ini

Salin tabel Karyawan

Memeriksa struktur dan data tabel EmployeeDemo

Mengisi Tabel dengan Data Hierarkis yang Ada

Untuk membuat tabel baru bernama NewOrg

Membuat tabel sementara bernama #Children

Mengisi tabel NewOrg

Mengoptimalkan Tabel NewOrg

Membuat indeks pada tabel NewOrg untuk pencarian yang efisien

Menghapus kolom yang tidak perlu

Ganti tabel asli dengan tabel baru

Langkah berikutnya

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan: