Lektion 1: Konvertieren einer Tabelle in eine hierarchische Struktur

Artikel
04/26/2023

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Kunden mit Tabellen, die hierarchische Beziehungen mithilfe von Selbstjoins darstellen, können diese Tabellen in eine hierarchische Struktur konvertieren, indem Sie diese Lektion als Richtlinie verwenden. Es ist relativ leicht, von dieser Darstellung zu einer mit hierarchyidzu migrieren. Nach der Migration verfügen die Benutzer über ein grundlegendes Verständnis hierarchischer Darstellungen, die für effizientere Abfragen auf verschiedene Weise indiziert werden können.

In dieser Lektion wird eine vorhandene Tabelle untersucht und eine neue Tabelle mit einer hierarchyid -Spalte erstellt. Daraufhin wird diese Tabelle mit den Daten der Quelltabelle gefüllt, und schließlich werden drei Indizierungsstrategien dargestellt. Diese Lektion enthält die folgenden Themen:

Voraussetzungen

Zur Durchführung dieses Tutorials benötigen Sie SQL Server Management Studio, Zugriff auf einen Server, auf dem SQL-Server ausgeführt wird, und eine AdventureWorks-Datenbank.

Installieren Sie SQL Server Management Studio.
Installieren Sie die SQL Server 2017 Developer Edition.
Laden Sie eine AdventureWorks-Beispieldatenbank herunter.

Anweisungen zum Wiederherstellen von Datenbanken in SSMS finden Sie hier: Wiederherstellen einer Datenbank.

Untersuchen der aktuellen Struktur der Mitarbeitertabelle

Die Beispieldatenbank AdventureWorks2022 enthält im Schema HumanResources die Tabelle Employee . Um Änderungen an der ursprünglichen Tabelle zu vermeiden, wird in diesem Schritt eine Kopie der Tabelle Employee mit dem Namen EmployeeDemoerstellt. Um das Beispiel zu vereinfachen, kopieren Sie nur fünf Spalten aus der ursprünglichen Tabelle. Dann fragen Sie die Tabelle HumanResources.EmployeeDemo ab, um zu sehen, wie die Daten in einer Tabelle strukturiert werden, wenn der hierarchyid -Datentyp nicht verwendet wird.

Kopieren der Mitarbeitertabelle

Führen Sie in einem Abfrage-Editorfenster den folgenden Code aus, um die Tabellenstruktur und die Daten der Tabelle Employee in eine neue Tabelle namens EmployeeDemozu kopieren. Da die ursprüngliche Tabelle bereits HierarchyID verwendet, vereinfacht diese Abfrage die Hierarchie, um den Manager des Mitarbeiters abzurufen. Im weiteren Verlauf dieser Lektion rekonstruieren Sie diese Hierarchie.

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

Untersuchen der Struktur und Daten der Tabelle „EmployeeDemo“

Die neue Tabelle EmployeeDemo stellt eine typische Tabelle einer vorhandenen Datenbank dar, die in eine neue Struktur migriert werden soll. Führen Sie in einem Abfrage-Editorfenster den folgenden Code aus, um zu zeigen, wie die Mitarbeiter-Manager-Beziehungen mithilfe eines Selbstjoins dargestellt werden:

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

Hier sehen Sie das Ergebnis.

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

Die Ausgabe umfasst insgesamt 290 Zeilen.

Beachten Sie, dass die ORDER BY -Klausel bewirkt, dass die Mitarbeiter, die einer Managementebene direkt unterstellt sind, jeweils zusammen aufgelistet werden. So werden beispielsweise die sieben Mitarbeiter, die MgrID 1 (ken0) direkt unterstellt sind, nebeneinander aufgeführt. Es ist zwar nicht unmöglich, aber sehr viel schwieriger, alle Mitarbeiter zu gruppieren, die MgrID 1 auch indirekt unterstellt sind.

Auffüllen einer Tabelle mit vorhandenen hierarchischen Daten

In dieser Aufgabe wird eine neue Tabelle erstellt und mit den Daten aus der Tabelle EmployeeDemo aufgefüllt. Diese Aufgabe umfasst die folgenden Schritte:

Erstellen Sie eine neue Tabelle, die eine hierarchyid -Spalte enthält. Diese Spalte könnte die vorhandenen Spalten EmployeeID und ManagerID ersetzen. Sie behalten diese Spalten jedoch bei, weil bestehende Anwendungen möglicherweise auf diese Spalten verweisen und weil dann die Daten nach der Übertragung leichter verständlich sind. Gemäß der Tabellendefinition ist OrgNode der Primärschlüssel, so dass diese Spalte eindeutige Werte enthalten muss. Der gruppierte Index der Spalte OrgNode speichert das Datum in OrgNode -Sequenz.
Erstellen Sie eine temporäre Tabelle, mit deren Hilfe verfolgt wird, wie viele Mitarbeiter jedem Manager direkt unterstellt sind.
Füllen Sie die neue Tabelle mit Daten aus der Tabelle EmployeeDemo auf.

So erstellen Sie eine neue Tabelle namens NewOrg

Führen Sie in einem Abfrage-Editorfenster den folgenden Code aus, um eine einfache Tabelle namens HumanResources.NewOrgzu erstellen.

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

Erstellen einer temporären Tabelle namens „#Children“

Erstellen Sie eine temporäre Tabelle namens #Children mit einer Spalte namens Num , in der für jeden Knoten die Anzahl der untergeordneten Elemente verzeichnet wird:
```
CREATE TABLE #Children   
   (  
    EmployeeID int,  
    ManagerID int,  
    Num int  
);  
GO  
```
Fügen Sie einen Index hinzu, der die Abfrage, mit der die Tabelle NewOrg aufgefüllt wird, erheblich beschleunigt:
```
CREATE CLUSTERED INDEX tmpind ON #Children(ManagerID, EmployeeID);  
GO  
```

Auffüllen der Tabelle „NewOrg“

In rekursiven Abfragen sind Unterabfragen mit Aggregaten nicht zulässig. Füllen Sie die Tabelle #Children stattdessen mit folgendem Code auf, in dem die ROW_NUMBER() -Methode zum Auffüllen der Spalte Num verwendet wird:
```
INSERT #Children (EmployeeID, ManagerID, Num)  
SELECT EmployeeID, ManagerID,  
  ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID)   
FROM HumanResources.EmployeeDemo  
GO 
```

Überprüfen Sie die Tabelle #Children . Die Spalte Num enthält fortlaufende Nummern für die einzelnen Manager.

SELECT * FROM #Children ORDER BY ManagerID, Num  
GO

Hier sehen Sie das Ergebnis.

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

Füllen Sie die Tabelle NewOrg auf. Verwenden Sie die Methoden GetRoot und ToString, um die Werte der Spalte Num zu verketten, so dass sie dem hierarchyid -Format entsprechen, und aktualisieren Sie anschließend die Spalte OrgNode mit den resultierenden hierarchischen Werten:

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

Eine hierarchyid -Spalte ist verständlicher, wenn sie in das Zeichenformat konvertiert wird. Überprüfen Sie die Daten der Tabelle NewOrg , indem Sie den folgenden Code ausführen, der zwei Darstellungen der Spalte OrgNode enthält:
```
SELECT OrgNode.ToString() AS LogicalNode, *   
FROM HumanResources.NewOrg   
ORDER BY LogicalNode;  
GO  
```
Die Spalte LogicalNode konvertiert die hierarchyid -Spalte in ein lesbareres Textformat, das die Hierarchie darstellt. In den restlichen Aufgaben werden Sie die ToString() -Methode verwenden, um die hierarchyid -Spalten im logischen Format anzuzeigen.
Löschen Sie die temporäre Tabelle, die nicht mehr benötigt wird:
```
DROP TABLE #Children  
GO  
```

Optimieren der NewOrg-Tabelle

Die NewOrd -Tabelle, die Sie in der Aufgabe Populating a Table with Existing Hierarchical Data erstellt haben, enthält alle Angestellteninformationen und stellt die hierarchische Struktur mithilfe des hierarchyid -Datentyps dar. In dieser Aufgabe werden neue Indizes hinzugefügt, die das Suchen in der hierarchyid -Spalte unterstützen.

Die Spalte hierarchyid (OrgNode) ist der Primärschlüssel für die NewOrg -Tabelle. Als die Tabelle erstellt wurde, enthielt sie den gruppierten Index PK_NewOrg_OrgNode , der die Eindeutigkeit der OrgNode -Spalte erzwingen sollte. Dieser gruppierte Index unterstützt auch eine Tiefensuche in der Tabelle.

Erstellen eines Index für die Tabelle „NewOrg“ für effiziente Suchvorgänge

Verwenden Sie zur Unterstützung von Abfragen der gleichen Ebene in der Hierarchie die GetLevel -Methode, um eine berechnete Spalte zu erstellen, welche die Ebene in der Hierarchie enthält. Erstellen Sie dann für diese und die Hierarchyid-Spalte einen zusammengesetzten Index. Führen Sie den folgenden Code aus, um die berechnete Spalte und den Breitensuchindex zu erstellen:
```
ALTER TABLE HumanResources.NewOrg   
   ADD H_Level AS OrgNode.GetLevel() ;  
CREATE UNIQUE INDEX EmpBFInd   
   ON HumanResources.NewOrg(H_Level, OrgNode) ;  
GO  
```
Erstellen Sie für die Spalte EmployeeID einen eindeutigen Index. Dies ist der übliche Singleton-Suchvorgang nach einem einzelnen Mitarbeiter entsprechend der EmployeeID -Nummer. Führen Sie den folgenden Code aus, um für EmployeeIDeinen Index zu erstellen:
```
CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;  
GO
```

Fügen Sie folgenden Code aus, um die Daten der Tabelle in der Reihenfolge jedes der drei Indizes abzurufen.

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

Vergleichen Sie die Resultsets, um zu sehen, wie die Reihenfolge in jedem Indextyp gespeichert wird. Im Folgenden werden nur die ersten vier Zeilen jeder Ausgabe gezeigt.

Hier sehen Sie das Ergebnis.

Tiefensuchindex: Mitarbeiterdatensätze sind angrenzend an den ihres Managers gespeichert.

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

Sortiert nachEmployeeID: Die Zeilen werden in der Reihenfolge der EmployeeID gespeichert.

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

Hinweis

Diagramme, die den Unterschied zwischen einem Tiefensuchindex und einem Breitensuchindex zeigen, finden Sie unter Hierarchische Daten (SQL Server).

Löschen der unnötigen Spalten

Die Spalte ManagerID stellt die Mitarbeiter-/Managerbeziehung dar, die jetzt von der Spalte OrgNode dargestellt wird. Wenn andere Anwendungen die Spalte ManagerID nicht benötigen, könnten Sie diese Spalte löschen, indem Sie die folgende Anweisung verwenden:
```
ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;  
GO  
```
Die Spalte EmployeeID ist ebenfalls überflüssig. Jeder Mitarbeiter wird bereits durch die Spalte OrgNode eindeutig identifiziert. Wenn andere Anwendungen die Spalte EmployeeID nicht benötigen, könnten Sie den Index und dann die Spalte löschen, indem Sie folgenden Code verwenden:
```
DROP INDEX EmpIDs_unq ON HumanResources.NewOrg ;  
ALTER TABLE HumanResources.NewOrg DROP COLUMN EmployeeID ;  
GO  
```

Ersetzen der ursprünglichen durch die neue Tabelle

Wenn die ursprüngliche Tabelle irgendwelche zusätzlichen Indizes oder Einschränkungen enthält, dann fügen Sie diese der Tabelle NewOrg hinzu.
So ersetzen Sie die alte EmployeeDemo -Tabelle durch die neue Tabelle. Führen Sie folgenden Code aus, um die alte Tabelle zu löschen und dann die neue Tabelle mit dem Namen der alten Tabelle zu benennen:
```
DROP TABLE HumanResources.EmployeeDemo ;  
GO  
sp_rename 'HumanResources.NewOrg', 'EmployeeDemo' ;  
GO  
```
Führen Sie den folgenden Code aus, um die neue Tabelle zu untersuchen:
```
SELECT * FROM HumanResources.EmployeeDemo ;  
```

Nächste Schritte

Im nächsten Artikel lernen Sie, Daten in einer hierarchischen Tabelle zu erstellen und zu verwalten.

Zum nächsten Artikel wechseln, um mehr zu erfahren:

Nächste Schritte

Freigeben über

Lektion 1: Konvertieren einer Tabelle in eine hierarchische Struktur

Voraussetzungen

Untersuchen der aktuellen Struktur der Mitarbeitertabelle

Kopieren der Mitarbeitertabelle

Untersuchen der Struktur und Daten der Tabelle „EmployeeDemo“

Auffüllen einer Tabelle mit vorhandenen hierarchischen Daten

So erstellen Sie eine neue Tabelle namens NewOrg

Erstellen einer temporären Tabelle namens „#Children“

Auffüllen der Tabelle „NewOrg“

Optimieren der NewOrg-Tabelle

Erstellen eines Index für die Tabelle „NewOrg“ für effiziente Suchvorgänge

Löschen der unnötigen Spalten

Ersetzen der ursprünglichen durch die neue Tabelle

Nächste Schritte

Feedback

Zusätzliche Ressourcen