Utilisation du type de données hierarchyid

Cette rubrique comprend des informations sur les activités courantes de gestion et d'interrogation d'une arborescence de données hiérarchique.

Dans cette rubrique

Gestion d'une arborescence à l'aide de hierarchyid

Application d'une arborescence

Exemple utilisant CLR

Déplacement de sous-arborescences

Gestion d'une arborescence à l'aide de hierarchyid

Bien qu'une colonne hierarchyid ne représente pas nécessairement une arborescence, une application peut facilement faire en sorte que ce soit le cas.

• Lorsque vous générez de nouvelles valeurs, effectuez l'une des opérations suivantes :

  • Effectuez le suivi du dernier numéro enfant dans la ligne parent.

  • Calculez le dernier enfant. Cette opération nécessite un index à largeur prioritaire pour être efficace.

• Appliquez l'unicité en créant un index unique sur la colonne, éventuellement en tant que partie d'une clé de clustering. Pour vous assurer que les valeurs uniques sont insérées, effectuez l'une des opérations suivantes :

  • détermination et insertion de l'unicité de chaque nouveau nœud enfant dans une transaction sérialisable ;

  • détection des échecs de violation de clés uniques et nouvelle tentative.

Exemple utilisant la détection d'erreurs

Dans l'exemple suivant, le code exemple calcule la nouvelle valeur enfant EmployeeId, puis détecte toute violation de clé et retourne au marqueur INS_EMP pour recalculer la valeur EmployeeId de la nouvelle ligne :

USE AdventureWorks ;
GO

CREATE TABLE Org_T1
   (
    EmployeeId hierarchyid PRIMARY KEY,
    OrgLevel AS EmployeeId.GetLevel(),
    EmployeeName nvarchar(50) 
   ) ;
GO

CREATE INDEX Org_BreadthFirst ON Org_T1(OrgLevel, EmployeeId)
GO

CREATE PROCEDURE AddEmp(@mgrid hierarchyid, @EmpName nvarchar(50) ) 
AS
BEGIN
    DECLARE @last_child hierarchyid
INS_EMP: 
    SELECT @last_child = MAX(EmployeeId) FROM Org_T1 
    WHERE EmployeeId.GetAncestor(1) = @mgrid
INSERT Org_T1 (EmployeeId, EmployeeName)
SELECT @mgrid.GetDescendant(@last_child, NULL), @EmpName 
-- On error, return to INS_EMP to recompute @last_child
IF @@error <> 0 GOTO INS_EMP 
END ;
GO

Exemple utilisant une transaction sérialisable

L'index Org_BreadthFirst assure que la détermination de @last_child est une recherche de plage. En plus des autres cas d'erreur qu'une application peut être amenée à vérifier, une violation de clé en double après l'insertion indique une tentative d'ajouter plusieurs employés ayant le même ID. Par conséquent, @last_child doit être recalculé. Le code suivant utilise une transaction sérialisable et un index à largeur prioritaire pour calculer la nouvelle valeur de nœud :

CREATE TABLE Org_T2
    (
    EmployeeId hierarchyid PRIMARY KEY,
    LastChild hierarchyid, 
    EmployeeName nvarchar(50) 
    ) ;
GO

CREATE PROCEDURE AddEmp(@mgrid hierarchyid, @EmpName nvarchar(50)) 
AS
BEGIN
DECLARE @last_child hierarchyid
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 

UPDATE Org_T2 
SET @last_child = LastChild = EmployeeId.GetDescendant(LastChild,NULL)
WHERE EmployeeId = @mgrid
INSERT Org_T2 (EmployeeId, EmployeeName) 
    VALUES(@last_child, @EmpName)
COMMIT
END ;

Le code suivant remplit la table avec trois lignes et retourne les résultats :

INSERT Org_T2 (EmployeeId, EmployeeName) 
    VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2

Voici l'ensemble des résultats.

EmployeeId LastChild EmployeeName
---------- --------- ------------
0x        0x58       David
0x58      0x5AC0     Sariya
0x5AC0    NULL       Mary

Retour au début

Application d'une arborescence

Les exemples ci-dessus illustrent comment une application peut garantir la conservation d'une arborescence. Pour appliquer une arborescence via des contraintes, une colonne calculée qui définit le parent de chaque nœud peut être créée avec une contrainte de clé étrangère vers l'ID de clé primaire.

CREATE TABLE Org_T3
(
   EmployeeId hierarchyid PRIMARY KEY,
   ParentId AS EmployeeId.GetAncestor(1) PERSISTED  
      REFERENCES Org_T3(EmployeeId),
   LastChild hierarchyid, 
   EmployeeName nvarchar(50)
)
GO

Cette méthode d'application d'une relation est préférable lorsqu'un code non fiable pour maintenir l'arborescence hiérarchique dispose d'un accès DML direct à la table. Cette méthode est susceptible de réduire les performances car la contrainte doit être vérifiée à chaque opération DML.

Retour au début

Exemple utilisant CLR

La recherche de l'ancêtre commun le plus bas est une opération courante impliquant deux nœuds dans une hiérarchie. Cela peut être écrit dans Transact-SQL ou CLR, car le type hierarchyid est disponible pour les deux. L'utilisation de CLR est recommandée car les performances seront plus rapides.

Utilisez le code CLR suivant pour rechercher des ancêtres de liste et trouver l'ancêtre commun le plus bas :

using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;

public partial class HierarchyId_Operations
{
    [SqlFunction(FillRowMethodName = "FillRow_ListAncestors")]
    public static IEnumerable ListAncestors(SqlHierarchyId h)
    {
        while (!h.IsNull)
        {
            yield return (h);
            h = h.GetAncestor(1);
        }
    }
    public static void FillRow_ListAncestors(Object obj, out SqlHierarchyId ancestor)
    {
        ancestor = (SqlHierarchyId)obj;
    }

    public static HierarchyId CommonAncestor(SqlHierarchyId h1, HierarchyId h2)
    {
        while (!h1.IsDescendant(h2))
            h1 = h1.GetAncestor(1);
        
        return h1;
    }
}

Pour utiliser les méthodes ListAncestor et CommonAncestor dans les exemples Transact-SQL suivants, générez la DLL et créez l'assembly HierarchyId_Operations dans SQL Server en exécutant un code semblable à celui-ci :

CREATE ASSEMBLY HierarchyId_Operations 
FROM '<path to DLL>\ListAncestors.dll'
GO

Retour au début

Répertorier les ancêtres

La création d'une liste d'ancêtres d'un nœud est une opération courante, par exemple pour afficher la position au sein d'une organisation. Pour ce faire, il est par exemple possible d'utiliser une fonction table à l'aide de la classe HierarchyId_Operations définie ci-dessus :

Utilisation de Transact-SQL :

CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO

Exemple d'utilisation :

DECLARE @h hierarchyid
SELECT @h = OrgNode 
FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\janice0' -- /1/1/5/2/

SELECT LoginID, OrgNode.ToString() AS LogicalNode
FROM HumanResources.EmployeeDemo AS ED
JOIN ListAncestors(@h) AS A 
   ON ED.OrgNode = A.Node
GO

Recherche de l'ancêtre commun le plus bas

À l'aide de la classe HierarchyId_Operations définie ci-dessus, créez la fonction Transact-SQL suivante pour rechercher l'ancêtre commun le plus bas impliquant deux nœuds dans une hiérarchie :

CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO

Exemple d'utilisation :

DECLARE @h1 hierarchyid, @h2 hierarchyid

SELECT @h1 = OrgNode 
FROM  HumanResources.EmployeeDemo 
WHERE LoginID = 'adventure-works\jossef0' -- Node is /1/1/3/

SELECT @h2 = OrgNode 
FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\janice0' -- Node is /1/1/5/2/

SELECT OrgNode.ToString() AS LogicalNode, LoginID 
FROM HumanResources.EmployeeDemo  
WHERE OrgNode = dbo.CommonAncestor(@h1, @h2) ;

Le nœud résultant est /1/1/

Retour au début

Déplacement de sous-arborescences

Une autre opération courante concerne le déplacement de sous-arborescences. La procédure suivante prend la sous-arborescence de @oldMgr pour en faire une sous-arborescence de @newMgr (en y incluant @oldMgr).

CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) )
AS
BEGIN
DECLARE @nold hierarchyid, @nnew hierarchyid
SELECT @nold = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @oldMgr ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @nnew = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @newMgr ;

SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL) 
FROM HumanResources.EmployeeDemo WHERE OrgNode.GetAncestor(1)=@nnew ;

UPDATE HumanResources.EmployeeDemo  
SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew)
WHERE @nold.IsDescendant(OrgNode) = 1 ;

COMMIT TRANSACTION
END ;
GO

Voir aussi

Référence

hierarchyid (Transact-SQL)

Concepts

Utilisation des types de données hierarchyid (moteur de base de données)

Référence de méthodes de type de données hierarchyid

Didacticiel : utilisation du type de données hierarchyid