Leçon 1 : Conversion d’une table en une structure hiérarchique

Article
04/26/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

Les clients qui ont des tables utilisant des jointures réflexives pour exprimer des relations hiérarchiques peuvent convertir leurs tables en structure hiérarchique en suivant les procédures fournies dans cette leçon. Il est relativement facile d'effectuer une migration de cette représentation vers une autre à l'aide de hierarchyid. Après la migration, les utilisateurs disposeront d'une représentation hiérarchique compacte et facile à comprendre, qui peut être indexée de plusieurs façons pour que les requêtes soient efficaces.

Cette leçon examine une table existante, crée une table contenant une colonne hierarchyid , remplit la table avec les données de la table source, puis illustre trois stratégies d'indexation. Cette leçon contient les rubriques suivantes :

Prerequisites

Pour suivre ce tutoriel, vous avez besoin de SQL Server Management Studio, de l’accès à un serveur qui exécute SQL Server et d’une base de données AdventureWorks.

Installez SQL Server Management Studio.
Installez SQL Server 2017 Developer Edition.
Téléchargez un exemple de base de données AdventureWorks.

Les instructions de restauration de bases de données dans SSMS se trouvent ici : Restaurer une base de données.

Étudier la structure actuelle de la table Employee

L'exemple de base de données AdventureWorks2022 contient une table Employee dans le schéma HumanResources . Afin d'éviter de modifier la table d'origine, cette étape effectue une copie de la table Employee , nommée EmployeeDemo. Pour simplifier l'exemple, vous ne copiez que cinq colonnes de la table d'origine. Vous interrogez ensuite la table HumanResources.EmployeeDemo pour vérifier comment sont structurées les données dans une table sans utiliser le type de données hierarchyid .

Copier la table Employee

Dans une fenêtre de l'Éditeur de requête, exécutez le code suivant pour copier la structure et les données de la table Employee dans une nouvelle table nommée EmployeeDemo. Étant donné que la table d’origine utilise déjà hierarchyid, cette requête aplatit essentiellement la hiérarchie pour obtenir le nom du responsable de l’employé. Dans les parties suivantes de cette leçon, nous reconstruirons cette hiérarchie.

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

Examiner la structure et les données de la table EmployeeDemo

Cette nouvelle table EmployeeDemo représente une table classique dans une base de données existante que vous pouvez souhaiter migrer vers une nouvelle structure. Dans une fenêtre de l'Éditeur de requête, exécutez le code suivant pour voir comment la table utilise une jointure réflexive pour afficher les relations employé/responsable :

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

Voici le jeu de résultats obtenu.

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

Les résultats se poursuivent pour un total de 290 lignes.

Notez que le résultat de la clause ORDER BY a provoqué le regroupement des subordonnés directs de chaque niveau de gestion. Par exemple, les sept subordonnés directs de MgrID 1 (ken0) sont regroupés les uns à côté des autres. Il est possible, mais beaucoup plus difficile, de regrouper tous ceux dont MgrID 1 est le supérieur final.

Remplir une table avec des données hiérarchiques existantes

Cette tâche crée une table et la remplit avec les données de la table EmployeeDemo . Les étapes de cette tâche sont les suivantes :

Créez une table qui contient une colonne hierarchyid . Cette colonne pourrait remplacer les colonnes EmployeeID et ManagerID existantes. Toutefois, vous conserverez ces colonnes. Cela s'explique par le fait que les applications existantes peuvent faire référence à ces colonnes. De même, cela peut vous aider à comprendre les données après le transfert. La définition de table spécifie que OrgNode est la clé primaire, ce qui exige que la colonne contienne des valeurs uniques. L’index cluster sur la colonne OrgNode stockera la date dans la séquence OrgNode .
Créez une table temporaire utilisée pour effectuer le suivi du nombre d'employés dont chaque responsable est le supérieur direct.
Remplissez la nouvelle table en utilisant les données de la table EmployeeDemo .

Pour créer une table nommée NewOrg

Dans une fenêtre de l’Éditeur de requête, exécutez le code suivant pour créer une table nommée HumanResources.NewOrg:

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

Créer une table temporaire nommée #Children

Créez une table temporaire nommée #Children avec une colonne nommée Num qui contiendra le nombre d’enfants pour chaque nœud :
```
CREATE TABLE #Children   
   (  
    EmployeeID int,  
    ManagerID int,  
    Num int  
);  
GO  
```
Ajoutez un index qui accélérera considérablement la requête qui remplit la table NewOrg :
```
CREATE CLUSTERED INDEX tmpind ON #Children(ManagerID, EmployeeID);  
GO  
```

Remplir la table NewOrg

Les requêtes récursives interdisent les sous-requêtes avec agrégats. À la place, remplissez la table #Children avec le code suivant, qui utilise la méthode ROW_NUMBER() pour remplir la colonne Num :
```
INSERT #Children (EmployeeID, ManagerID, Num)  
SELECT EmployeeID, ManagerID,  
  ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID)   
FROM HumanResources.EmployeeDemo  
GO 
```

Examinez la table #Children . Notez la façon dont la colonne Num contient des numéros séquentiels pour chaque responsable.

SELECT * FROM #Children ORDER BY ManagerID, Num  
GO

Voici le jeu de résultats obtenu.

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

Remplissez la table NewOrg . Utilisez les méthodes GetRoot et ToString pour concaténer les valeurs Num au format hierarchyid , puis mettez à jour la colonne OrgNode avec les valeurs hiérarchiques résultantes :

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

Une colonne hierarchyid est plus compréhensible quand vous la convertissez au format caractère. Vérifiez les données de la table NewOrg en exécutant le code suivant, qui contient deux représentations de la colonne OrgNode :
```
SELECT OrgNode.ToString() AS LogicalNode, *   
FROM HumanResources.NewOrg   
ORDER BY LogicalNode;  
GO  
```
La colonne LogicalNode convertit la colonne hierarchyid en format texte plus lisible qui représente la hiérarchie. Dans les tâches restantes, vous utiliserez la méthode ToString() pour afficher le format logique des colonnes hierarchyid .
Supprimez la table temporaire, qui n'est plus nécessaire :
```
DROP TABLE #Children  
GO  
```

Optimisation de la table NewOrg

La table NewOrd que vous avez créée dans la tâche Remplissage d’une table avec des données hiérarchiques existantes contient toutes les informations relatives aux employés et représente la structure hiérarchique à l’aide d’un type de données hierarchyid . Cette tâche ajoute de nouveaux index pour prendre en charge les recherches sur la colonne hierarchyid .

La colonne hierarchyid (OrgNode) est la clé primaire de la table NewOrg . Quand la table a été créée, elle contenait un index cluster nommé PK_NewOrg_OrgNode pour forcer l’unicité de la colonne OrgNode . Cet index cluster prend également en charge une recherche à profondeur prioritaire de la table.

Créer un index sur la table NewOrg pour des recherches efficaces

Pour faciliter les requêtes au même niveau de la hiérarchie, utilisez la méthode GetLevel pour créer une colonne calculée qui contient le niveau dans la hiérarchie. Créez ensuite un index composite sur le niveau et Hierarchyid. Exécutez le code suivant pour créer la colonne calculée et l'index à largeur prioritaire :
```
ALTER TABLE HumanResources.NewOrg   
   ADD H_Level AS OrgNode.GetLevel() ;  
CREATE UNIQUE INDEX EmpBFInd   
   ON HumanResources.NewOrg(H_Level, OrgNode) ;  
GO  
```
Créez un index unique sur la colonne EmployeeID . Il s’agit de la recherche singleton classique d’un seul employé par numéro EmployeeID . Exécutez le code suivant pour créer un index sur EmployeeID:
```
CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;  
GO
```

Exécutez le code suivant pour récupérer des données de la table dans l'ordre de chacun des trois index :

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

Comparez les jeux de résultats pour voir comment l'ordre est stocké dans chaque type d'index. Seules les quatre premières lignes de chaque de sortie suivent.

Voici le jeu de résultats obtenu.

Index à profondeur prioritaire : les enregistrements d'employés sont stockés à proximité de leur responsable.

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

Index avecEmployeeIDprioritaire : les lignes sont stockées dans l’ordre des 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

Note

Pour les diagrammes qui affichent la différence entre un index à profondeur prioritaire et un index à largeur prioritaire, consultez Données hiérarchiques (SQL Server).

Supprimer les colonnes inutiles

La colonne ManagerID représente la relation employé/responsable, qui est maintenant représentée par la colonne OrgNode . Si les autres applications n’ont pas besoin de la colonne ManagerID , vous pouvez envisager de la supprimer à l’aide de l’instruction suivante :
```
ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;  
GO  
```
La colonne EmployeeID est également redondante. La colonne OrgNode identifie chaque employé de façon univoque. Si les autres applications n’ont pas besoin de la colonne EmployeeID , vous pouvez envisager de supprimer l’index puis la colonne, en utilisant le code suivant :
```
DROP INDEX EmpIDs_unq ON HumanResources.NewOrg ;  
ALTER TABLE HumanResources.NewOrg DROP COLUMN EmployeeID ;  
GO  
```

Remplacer la table d’origine par la nouvelle table

Si votre table d’origine contenait des index ou contraintes supplémentaires, ajoutez-les à la table NewOrg .
Remplacez l’ancienne table EmployeeDemo par la nouvelle table. Exécutez le code suivant pour supprimer l'ancienne table, puis renommez la nouvelle table avec l'ancien nom :
```
DROP TABLE HumanResources.EmployeeDemo ;  
GO  
sp_rename 'HumanResources.NewOrg', 'EmployeeDemo' ;  
GO  
```
Exécutez le code suivant pour examiner la table finale :
```
SELECT * FROM HumanResources.EmployeeDemo ;  
```

Étapes suivantes

L’article suivant vous apprend à créer et gérer des données dans une table hiérarchique.

Passez à l’article suivant pour en savoir plus :

Étapes suivantes

Leçon 1 : Conversion d’une table en une structure hiérarchique

Prerequisites

Étudier la structure actuelle de la table Employee

Copier la table Employee

Examiner la structure et les données de la table EmployeeDemo

Remplir une table avec des données hiérarchiques existantes

Pour créer une table nommée NewOrg

Créer une table temporaire nommée #Children

Remplir la table NewOrg

Optimisation de la table NewOrg

Créer un index sur la table NewOrg pour des recherches efficaces

Supprimer les colonnes inutiles

Remplacer la table d’origine par la nouvelle table

Étapes suivantes

Commentaires

Commentaires

Ressources supplémentaires