Optimisation de la table NewOrg

Article
05/07/2013

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.

Index cluster

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

Index non cluster

Cette étape crée deux index non cluster pour prendre en charge des recherches typiques.

Pour indexer la table NewOrg en vue d'effectuer 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 NewOrg 
   ADD H_Level AS OrgNode.GetLevel() ;
CREATE UNIQUE INDEX EmpBFInd 
   ON 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 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 NewOrg 
ORDER BY OrgNode;

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

SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID 
FROM 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 l'ensemble des résultats.

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

LogicalNode OrgNode H_Level EmployeeID LoginID

/ 0x 0 1 zarifin

/1/ 0x58 1 2 tplate

/1/1/ 0x5AC0 2 4 schai

/1/1/1/ 0x5AD6 3 9 jwang

/1/1/2/ 0x5ADA 3 10 malexander

/1/2/ 0x5B40 2 5 elang

/1/3/ 0x5BC0 2 6 gsmits

/2/ 0x68 1 3 hjensen

/2/1/ 0x6AC0 2 7 sdavis

/2/2/ 0x6B40 2 8 norint

Index affichant EmployeeID en priorité : les lignes sont stockées dans l'ordre des EmployeeID.

LogicalNode OrgNode H_Level EmployeeID LoginID

/ 0x 0 1 zarifin

/1/ 0x58 1 2 tplate

/2/ 0x68 1 3 hjensen

/1/1/ 0x5AC0 2 4 schai

/1/2/ 0x5B40 2 5 elang

/1/3/ 0x5BC0 2 6 gsmits

/2/1/ 0x6AC0 2 7 sdavis

/2/2/ 0x6B40 2 8 norint

/1/1/1/ 0x5AD6 3 9 jwang

/1/1/2/ 0x5ADA 3 10 malexander

[!REMARQUE]

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

Pour supprimer les colonnes inutiles

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

Pour 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 EmployeeDemo ;
GO
sp_rename 'NewOrg', EmployeeDemo ;
GO
```
Exécutez le code suivant pour examiner la table finale :
```
SELECT * FROM EmployeeDemo ;
```

Tâche suivante de la leçon

Résumé : Conversion d'une table en une structure hiérarchique

Partager via