Le type HierarchyID est un nouveau type de données de SQL Server 2008 (alias Katmai) qui permet de gérer comme son nom l’indique, des hiérarchies dans des tables relationnelles.

Nous allons voir dans cet article que ce nouveau type apporte des réponses dans la modélisation d’arborescences dans les bases de données. Il ajoute des fonctionnalités au langage T-SQL et améliore les performances. Cet article décrit en détail le nouveau type et fournit quelques exemples d’utilisation comparés à une utilisation plus classique à base de CTE.

 

L’article est disponible ici : Le type Hierarchy

Le code des démos est disponible à cette URL : code source

 

Note : le code de cet article fonctionne avec la CTP2 (fin juillet) de SQL Server 2008

Voici quelques extraits :

 

[…]

 

3.2 Utilisation de HierarchyID dans une table

Le type HierarchyID s’utilise comme n’importe quel autre type dans l’instruction de création de table (ou d’ajout de colonne). Dans les exemples qui suivent, nous allons travailler sur une table que nous appellerons Organization. Pour l’instant, elle ne contient qu’un champ de type HierarchyID et le nom de l’employé correspondant.

 

CREATE TABLE Organization

(

      EmployeeID hierarchyid NOT NULL,

EmployeeName nvarchar(50) NOT NULL

)

 

Nous allons remplir cette table avec des données en provenance de la table Employee de la base AdventureWorks. Pour ce remplissage, nous allons nous baser sur la CTE décrite plus haut. Pour déterminer la valeur de la racine, nous allons utiliser la fonction GetRoot() du type HierarchyID (à noter que GetRoot, comme les autres fonctions du type HierarchyID, est sensible à la casse) :

 

hierarchyid::GetRoot()

 

Pour déterminer la valeur des nœuds enfants, à chaque récursion nous utiliserons la fonction GetDescendant du nœud parent :

 

Parent.Node.GetDescendant(null, null)

 

Les paramètres de cette fonction permettent de placer le nœud enfant à une certaine position parmi les autres nœuds enfants (siblings).La CTE modifiée donne ce script T-SQL qui va copier la hiérarchie de la table Employee à la nouvelle table Organization bénéficiant du nouveau type.

 

 

WITH UpperHierarchy(EmpId, LastName, Node)

AS

(

    SELECT EmployeeId, LoginId, hierarchyid::GetRoot()

    FROM HumanResources.Employee

      WHERE ManagerId is Null

    UNION ALL

    SELECT Sub.EmployeeId, Sub.LoginId, Parent.Node.GetDescendant(null, null)

    FROM HumanResources.Employee AS Sub

            INNER JOIN UpperHierarchy AS Parent

                  ON Sub.ManagerId = Parent.EmpId

)

Insert Into dbo.Organization(EmployeeId, EmployeeName)

Select Node, LastName
From UpperHierarchy

 

[…]

 

En vrac, voici des exemples de requêtes simplifiées par le type HierarchyID.

6.1 Trouver tous les subordonnés

Plus besoin de CTE pour récupérer tout un pan de la hiérarchie. Pour connaître les subordonnés de quelqu’un, tous niveaux confondus, la requête est on ne peut plus simple :

 

Select *

From dbo.Organization

Where @BossNode.IsDescendant(EmployeeId)

 

Attention : un nœud est considéré comme son propre descendant, ce qui veut dire que la personne de référence sera présente dans la liste de ses subordonnés.

6.2 Trouver les responsables d’une personne

Pour remonter la chaîne des responsables d’une personne, on inverse la condition précédente :

 

Select *

From dbo.Organization

Where EmployeeId.IsDescendant(@BossNode)

 

6.3 Trouver les employés d’un niveau donné

Plus besoin de stocker et de maintenir le niveau dans la hiérarchie :

 

Select *

From dbo.Organization

Where EmployeeId.GetLevel() = 3

 

[…]

 

---