Lektion 1: Konvertera en tabell till en hierarkisk struktur

2025-02-04

Gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance SQL-databas i Förhandsversion av Microsoft Fabric

Kunder som har tabeller som använder självkopplingar för att uttrycka hierarkiska relationer kan konvertera sina tabeller till en hierarkisk struktur med hjälp av den här lektionen som en guide. Det är relativt enkelt att migrera från den här representationen till en som använder sig av hierarchyid. Efter migreringen har användarna en kompakt och lätt att förstå hierarkisk representation, som kan indexeras på flera sätt för effektiva frågor.

Den här lektionen undersöker en befintlig tabell, skapar en ny tabell som innehåller en hierarkiid kolumn, fyller tabellen med data från källtabellen och visar sedan tre indexeringsstrategier. Den här lektionen innehåller följande avsnitt:

Prerequisites

För att slutföra den här självstudien behöver du SQL Server Management Studio, åtkomst till en server som kör SQL Server och en AdventureWorks-databas.

Installera SQL Server Management Studio.
Installera SQL Server 2017 Developer Edition.
Ladda ned AdventureWorks-exempeldatabaser.

Anvisningar för att återställa databaser i SSMS finns här: Återställa en databas.

Granska den aktuella strukturen i arbetstagartabellen

Exempeldatabasen AdventureWorks2022 innehåller en tabell Employee i schemat HumanResources. För att undvika att ändra den ursprungliga tabellen gör det här steget en kopia av tabellen Employee med namnet EmployeeDemo. För att förenkla exemplet kopierar du bara fem kolumner från den ursprungliga tabellen. Sedan gör du en förfrågan till tabellen HumanResources.EmployeeDemo för att se hur datan är strukturerad i en tabell utan att använda datatypen hierarchyid.

Kopiera anställdstabellen

I ett frågeredigerare-fönster kör du följande kod för att kopiera tabellstrukturen och data från tabellen Employee till en ny tabell med namnet EmployeeDemo. Eftersom den ursprungliga tabellen redan använder hierarchyid, jämnar denna frågeställning i princip ut hierarkin för att hämta medarbetarens chef. I efterföljande delar av den här lektionen kommer vi att rekonstruera den här hierarkin.

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

Granska strukturen och data i tabellen EmployeeDemo

Den här nya EmployeeDemo-tabellen representerar en typisk tabell i en befintlig databas som du kanske vill migrera till en ny struktur. I ett frågeredigerare-fönster kör du följande kod för att visa hur tabellen använder en självkoppling för att visa relationerna mellan medarbetare och chef:

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

Här är resultatet.

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

Resultatet sträcker sig över totalt 290 rader.

Observera att satsen ORDER BY gjorde att utdata skulle visa en lista över direktrapporterna för varje hanteringsnivå tillsammans. Till exempel visas alla sju direktrapporterna för MgrID 1 (ken0) intill varandra. Även om det inte är omöjligt, är det mycket svårare att gruppera alla de som så småningom rapporterar till MgrID 1.

Fylla i en tabell med befintliga hierarkiska data

Den här uppgiften skapar en ny tabell och fyller den med data i tabellen EmployeeDemo. Den här uppgiften har följande steg:

Skapa en ny tabell som innehåller en kolumn av typen hierarchyid. Den här kolumnen kan ersätta de befintliga kolumnerna EmployeeID och ManagerID. Du behåller dock dessa kolumner. Det beror på att befintliga program kan referera till dessa kolumner och även för att hjälpa dig att förstå data efter överföringen. Tabelldefinitionen anger att OrgNode är den primära nyckeln, vilket kräver att kolumnen innehåller unika värden. Det klustrade indexet på kolumnen OrgNode kommer att lagra datumet i sekvensen OrgNode.
Skapa en tillfällig tabell som används för att spåra hur många anställda som rapporterar direkt till varje chef.
Fyll i den nya tabellen med hjälp av data från tabellen EmployeeDemo.

Skapa en ny tabell med namnet NewOrg

I fönstret Frågeredigeraren kör du följande kod för att skapa en ny tabell med namnet HumanResources.NewOrg:

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

Skapa en tillfällig tabell med namnet #Children

Skapa en tillfällig tabell med namnet #Children med en kolumn med namnet Num som innehåller antalet barn för varje nod.
```
CREATE TABLE #Children   
   (  
    EmployeeID int,  
    ManagerID int,  
    Num int  
);  
GO  
```
Lägg till ett index som avsevärt påskyndar frågan som fyller i tabellen NewOrg:
```
CREATE CLUSTERED INDEX tmpind ON #Children(ManagerID, EmployeeID);  
GO  
```

Fyll i tabellen NewOrg

Rekursiva frågor förbjuder underfrågor med aggregeringar. Fyll i I stället tabellen #Children med följande kod, som använder metoden ROW_NUMBER() för att fylla i kolumnen Num:

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

Granska tabellen #Children. Observera hur kolumnen Num innehåller sekventiella tal för varje chef.

SELECT * FROM #Children ORDER BY ManagerID, Num  
GO

Här är resultatet.

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

Fyll i tabellen NewOrg. Använd metoderna GetRoot och ToString för att sammanfoga värdena Num i hierarchyid format och uppdatera sedan kolumnen OrgNode med de resulterande hierarkiska värdena:

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

En hierarkiid kolumn är mer begriplig när du konverterar den till teckenformat. Granska data i tabellen NewOrg genom att köra följande kod, som innehåller två representationer av kolumnen OrgNode:
```
SELECT OrgNode.ToString() AS LogicalNode, *   
FROM HumanResources.NewOrg   
ORDER BY LogicalNode;  
GO  
```
Kolumnen LogicalNode konverterar kolumnen hierarchyid till ett mer läsbart textformulär som representerar hierarkin. I de återstående uppgifterna använder du metoden ToString() för att visa det logiska formatet för hierarchyid kolumner.
Släpp den temporära tabellen, som inte längre behövs:
```
DROP TABLE #Children  
GO  
```

Optimera NewOrg-tabellen

Den NewOrd-tabell som du skapade i aktiviteten Fyll i en tabell med befintliga hierarkiska data innehåller all information om medarbetaren och representerar den hierarkiska strukturen med hjälp av datatypen hierarchyid . Den här uppgiften lägger till nya index som stöd för sökningar i kolumnen hierarchyid.

Kolumnen hierarchyid (OrgNode) är primärnyckeln för tabellen NewOrg. När tabellen skapades innehöll den ett grupperat index med namnet PK_NewOrg_OrgNode för att framtvinga unikheten i kolumnen OrgNode. Det här klustrade indexet stöder också en djupsökning av tabellen.

Skapa index i NewOrg-tabellen för effektiva sökningar

Om du vill hjälpa frågor på samma nivå i hierarkin använder du metoden GetLevel för att skapa en beräknad kolumn som innehåller nivån i hierarkin. Skapa sedan ett sammansatt index på nivån och Hierarchyid. Kör följande kod för att skapa den beräknade kolumnen och bredfirst-indexet.
```
ALTER TABLE HumanResources.NewOrg   
   ADD H_Level AS OrgNode.GetLevel() ;  
CREATE UNIQUE INDEX EmpBFInd   
   ON HumanResources.NewOrg(H_Level, OrgNode) ;  
GO  
```
Skapa ett unikt index i kolumnen EmployeeID. Det här är den traditionella sökningen av en enskild anställd baserat på anställdas ID-nummer. Kör följande kod för att skapa ett index på EmployeeID:
```
CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;  
GO
```

Kör följande kod för att hämta data från tabellen i ordningen för vart och ett av de tre indexen:

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

Jämför resultatuppsättningarna för att se hur ordningen lagras i varje typ av index. Endast de första fyra raderna i varje utdatauppsättning följer.

Här är resultatet.

Djupförstagångsindex: Medarbetarregister lagras i nära anslutning till sina chefer.

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- första index: Rader lagras i EmployeeID-sekvens.

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

Note

Diagram som visar skillnaden mellan ett djupindex och ett breddindex finns i Hierarchical Data (SQL Server).

Släpp de onödiga kolumnerna

Kolumnen ManagerID representerar relationen med medarbetare/chef, som nu representeras av kolumnen OrgNode. Om andra program inte behöver kolumnen ManagerID kan du överväga att ta bort den med hjälp av följande instruktion:
```
ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;  
GO  
```
Kolumnen EmployeeID är också redundant. Kolumnen OrgNode identifierar varje medarbetare unikt. Om andra program inte behöver kolumnen EmployeeID kan du överväga att ta bort indexet och sedan kolumnen med hjälp av följande kod:
```
DROP INDEX EmpIDs_unq ON HumanResources.NewOrg ;  
ALTER TABLE HumanResources.NewOrg DROP COLUMN EmployeeID ;  
GO  
```

Ersätt den ursprungliga tabellen med den nya tabellen

Om den ursprungliga tabellen innehåller ytterligare index eller begränsningar lägger du till dem i tabellen NewOrg.
Ersätt den gamla EmployeeDemo-tabellen med den nya tabellen. Kör följande kod för att släppa den gamla tabellen och byt sedan namn på den nya tabellen med det gamla namnet:
```
DROP TABLE HumanResources.EmployeeDemo ;  
GO  
sp_rename 'HumanResources.NewOrg', 'EmployeeDemo' ;  
GO  
```
Kör följande kod för att undersöka den slutliga tabellen:
```
SELECT * FROM HumanResources.EmployeeDemo ;  
```

Nästa steg

I nästa artikel lär du dig att skapa och hantera data i en hierarkisk tabell.

Gå till nästa artikel om du vill veta mer:

Nästa steg

Feedback

Var den här sidan till hjälp?

Dela via

Lektion 1: Konvertera en tabell till en hierarkisk struktur

Prerequisites

Granska den aktuella strukturen i arbetstagartabellen

Kopiera anställdstabellen

Granska strukturen och data i tabellen EmployeeDemo

Fylla i en tabell med befintliga hierarkiska data

Skapa en ny tabell med namnet NewOrg

Skapa en tillfällig tabell med namnet #Children

Fyll i tabellen NewOrg

Optimera NewOrg-tabellen

Skapa index i NewOrg-tabellen för effektiva sökningar

Släpp de onödiga kolumnerna

Ersätt den ursprungliga tabellen med den nya tabellen

Nästa steg

Feedback

Ytterligare resurser