Datos jerárquicos (SQL Server)

Se aplica a:SQL Server Azure SQL DatabaseInstancia administrada de Azure SQLBase de datos SQL en Microsoft Fabric

El tipo de datos hierarchyid integrado facilita el almacenamiento y la consulta de datos jerárquicos. hierarchyid se optimiza para representar los árboles, que son el tipo más común de datos jerárquicos.

Los datos jerárquicos se definen como un conjunto de elementos de datos que se relacionan entre sí mediante relaciones jerárquicas. Las relaciones jerárquicas existen allí donde un elemento de los datos es el elemento primario de otro elemento. Entre los ejemplos de datos jerárquicos que se almacenan normalmente en las bases de datos se incluyen los siguientes elementos:

Una estructura organizativa
Un sistema de archivos
Un conjunto de tareas de un proyecto
Una taxonomía de términos de idioma
Un gráfico de vínculos entre páginas web

Use hierarchyid como tipo de datos para crear tablas con una estructura jerárquica o para describir la estructura jerárquica de datos almacenados en otra ubicación. Use las funciones hierarchyid de Transact-SQL para consultar y administrar los datos jerárquicos.

Propiedades importantes

Un valor del tipo de datos hierarchyid representa una posición en una jerarquía de árbol. Los valores de hierarchyid tienen las siguientes propiedades.

Extremadamente compacto

El número medio de bits que se necesitan para representar un nodo en un árbol con n nodos depende del factor de ramificación medio (el número medio de hijos de un nodo). Para fanouts pequeños (0-7), el tamaño es de aproximadamente $6\log{A}{n}$ bits, donde A es el fanout medio. Un nodo en una jerarquía organizativa de 100 000 personas con un fanout medio de seis niveles ocupa unos 38 bits. Esto se redondea a 40 bits (o 5 bytes) para el almacenamiento.
La comparación se realiza en orden de profundidad

Dados los valores hierarchyida y b, a < b significa que a precede a b en un recorrido en profundidad del árbol. Los índices de los tipos de datos hierarchyid están en orden con prioridad a la profundidad y los nodos cercanos entre sí en un corte transversal de prioridad a la profundidad se almacenan casi uno junto a otro. Por ejemplo, los descendientes de un registro se almacenan junto a dicho registro.
Admite inserciones y eliminaciones arbitrarias

Mediante el método GetDescendant (motor de base de datos), siempre es posible generar un nodo hermano a la derecha de cualquier nodo dado, a la izquierda de cualquier nodo dado o entre dos nodos hermanos cualesquiera. La propiedad de comparación se mantiene cuando se insertan o eliminan un número arbitrario de nodos de la jerarquía. La mayoría de las inserciones y eliminaciones conservan la propiedad de compacidad. Sin embargo, las inserciones entre dos nodos generan valores hierarchyid con una representación ligeramente menos compacta.

Limitations

El tipo de datos hierarchyid tiene las siguientes limitaciones:

Una columna de tipo hierarchyid no representa automáticamente un árbol. Depende de la aplicación generar y asignar los valores hierarchyid de tal forma que la relación deseada entre las filas se refleje en los valores. Algunas aplicaciones pueden tener una columna de tipo hierarchyid que indica la ubicación en una jerarquía definida en otra tabla.
Corresponde a la aplicación gestionar la concurrencia al generar y asignar valores hierarchyid. No hay ninguna garantía de que los valores hierarchyid de una columna sean únicos, a menos que la aplicación use una restricción de clave única o se aplique singularidad a través de su lógica.
Las relaciones jerárquicas representadas por valores hierarchyid no se aplican como una relación de clave externa. Es posible, y a veces adecuado, establecer una relación jerárquica donde A tiene un elemento secundario B, de forma que A se elimina dejando a B con una relación con un registro no existente. Si este comportamiento no es aceptable, la aplicación debe consultar a los descendientes antes de eliminar los miembros primarios.

Cuándo utilizar alternativas a hierarchyid

Dos alternativas a hierarchyid para representar los datos jerárquicos son:

Parent/child
XML

Normalmente,hierarchyid es mejor opción en comparación con estas alternativas. Sin embargo, hay situaciones concretas, que se detallan en este artículo, donde es probable que las alternativas sean una mejor opción.

Parent/child

Cuando se utiliza el enfoque padre/hijo, cada fila contiene una referencia al elemento padre. La siguiente tabla define una tabla típica utilizada para contener las filas padre e hijo en una relación padre/hijo:

USE AdventureWorks2022;
GO

CREATE TABLE ParentChildOrg (
    BusinessEntityID INT PRIMARY KEY,
    ManagerId INT FOREIGN KEY REFERENCES ParentChildOrg(BusinessEntityID),
    EmployeeName NVARCHAR(50)
);
GO

Comparación entre padre/hijo y hierarchyid para operaciones comunes:

Las consultas de subárboles son significativamente más rápidas con hierarchyid.
Las consultas directas de descendientes son ligeramente más lentas con hierarchyid.
Mover los nodos que no son hoja es más lento con hierarchyid.
La inserción de nodos no hoja y la inserción o el desplazamiento de nodos hoja tienen la misma complejidad con hierarchyid.

La estructura padre-hijo puede ser una opción superior cuando se cumplan las siguientes condiciones:

El tamaño de la clave es crítico. Para el mismo número de nodos, un valor hierarchyid es igual o mayor que un valor de la familia de enteros (smallint, int, bigint). Esto solo es motivo para usar una estructura padre/hijo en casos excepcionales, porque hierarchyid ofrece una localidad de E/S significativamente mejor y una complejidad de CPU menor que las expresiones de tabla comunes necesarias al usar una estructura padre/hijo.
Las consultas raramente recorren todas las secciones de la jerarquía. Dicho de otro modo, las consultas normalmente se dirigen a un solo punto de la jerarquía. En estos casos, la colocalización no es importante. Por ejemplo, la relación padre-hijo es preferible cuando la tabla de organización solo se utiliza para procesar la nómina de empleados individuales.
Los subárboles que no son hojas se desplazan con frecuencia y el rendimiento es muy importante. En una representación padre/hijo, cambiar la ubicación de una fila en una jerarquía afecta a una única fila. Si se cambia la ubicación de una fila cuando se usa hierarchyid, afectará a n filas, donde n es el número de nodos de un subárbol que se están moviendo.

Si los subárboles no hoja se mueven con frecuencia y el rendimiento es importante, pero la mayoría de los movimientos se producen en un nivel bien definido de la jerarquía, considere la posibilidad de dividir los niveles superiores e inferiores en dos jerarquías. Esto convierte todos los movimientos en niveles de hoja de la jerarquía más alta. Por ejemplo, considere la posibilidad de tener una jerarquía de sitios web hospedada por un servicio. Los sitios contienen muchas páginas organizadas de forma jerárquica. Los sitios hospedados se pueden mover a otras ubicaciones en la jerarquía del sitio, pero las páginas subordinadas rara vez se reorganizan. Esto se podría representar mediante:
```
CREATE TABLE HostedSites (
    SiteId HIERARCHYID,
    PageId HIERARCHYID
);
GO
```

XML

Un documento XML es un árbol y, por lo tanto, una única instancia de tipo de datos XML puede representar una jerarquía completa. En SQL Server, cuando se crea un índice XML, los valores de hierarchyid se usan de manera interna para representar la posición en la jerarquía.

Utilizar el tipo de datos XML puede ser mejor opción cuando se cumplen todas las condiciones siguientes:

Siempre se almacena y se recupera la jerarquía completa.
La aplicación consume los datos en formato XML.
Las búsquedas por predicado están muy limitadas y no son críticas para el rendimiento.

Por ejemplo, cuando una aplicación realiza el seguimiento de varias organizaciones, siempre almacena y recupera la jerarquía de la organización completa y no consulta en una sola organización, entonces podría tener sentido utilizar una tabla con la forma siguiente:

CREATE TABLE XMLOrg (
    Orgid INT,
    Orgdata XML
);
GO

Estrategias de indización para los datos jerárquicos

Hay dos estrategias para indizar datos jerárquicos:

Depth-first

En un índice con prioridad de profundidad, las filas de un subárbol se almacenan unas junto a otras. Por ejemplo, todos los empleados al mando de un gerente se almacenan junto al registro de este último.

En un índice de profundidad, todos los nodos del subárbol de un nodo se colocan juntos. Por lo tanto, los índices de profundidad son eficientes para responder a consultas sobre subárboles, como: Busca todos los archivos de esta carpeta y sus subcarpetas
Breadth-first

Un índice con equilibrio de carga en amplitud almacena juntas las filas de cada nivel de la jerarquía. Por ejemplo, se almacenan unos junto a otros los registros de empleados que notifican directamente al mismo gerente.

En un índice de búsqueda en anchura, todos los hijos directos de un nodo se agrupan en el mismo lugar. Por lo tanto, los índices de búsqueda en anchura son eficaces para responder a consultas sobre hijos inmediatos, como por ejemplo: Busca todos los empleados que dependen directamente de este responsable.

La decisión de usar prioridad en profundidad, prioridad en anchura o ambas, y de cuál de ellas definir como clave de agrupación, si corresponde, depende de la importancia relativa de los tipos de consultas anteriores y de la importancia relativa de SELECT frente a las operaciones DML. Para obtener un ejemplo detallado de las estrategias de indización, consulte Tutorial: Using the hierarchyid Data Type.

Creación de índices

El método GetLevel() se puede usar para crear una ordenación con prioridad a la amplitud. En el ejemplo siguiente se han creado los índices con prioridad a la amplitud y con prioridad a la profundidad:

USE AdventureWorks2022;
GO

CREATE TABLE Organization (
    BusinessEntityID HIERARCHYID,
    OrgLevel AS BusinessEntityID.GetLevel(),
    EmployeeName NVARCHAR(50) NOT NULL
);
GO

CREATE CLUSTERED INDEX Org_Breadth_First
ON Organization (OrgLevel, BusinessEntityID);
GO

CREATE UNIQUE INDEX Org_Depth_First
ON Organization (BusinessEntityID);
GO

Examples

Los ejemplos de código de este artículo usan la base de datos de ejemplo de AdventureWorks2025 o AdventureWorksDW2025, que puede descargar de la página principal de Ejemplos de Microsoft SQL Server y proyectos de comunidad.

Ejemplo básico

El ejemplo siguiente es deliberadamente simplista para ayudarle a empezar. Cree primero una tabla que contenga algunos datos de geografía.

CREATE TABLE BasicDemo (
    [Level] HIERARCHYID NOT NULL,
    Location NVARCHAR(30) NOT NULL,
    LocationType NVARCHAR(9) NULL
);

Ahora inserte datos para algunos continentes, países o regiones, estados y ciudades.

INSERT BasicDemo
VALUES ('/1/', 'Europe', 'Continent'),
    ('/2/', 'South America', 'Continent'),
    ('/1/1/', 'France', 'Country'),
    ('/1/1/1/', 'Paris', 'City'),
    ('/1/2/1/', 'Madrid', 'City'),
    ('/1/2/', 'Spain', 'Country'),
    ('/3/', 'Antarctica', 'Continent'),
    ('/2/1/', 'Brazil', 'Country'),
    ('/2/1/1/', 'Brasilia', 'City'),
    ('/2/1/2/', 'Bahia', 'State'),
    ('/2/1/2/1/', 'Salvador', 'City'),
    ('/3/1/', 'McMurdo Station', 'City');

Seleccione los datos, agregando una columna que convierta los datos de nivel a un valor de texto que sea fácil de entender. Esta consulta también ordena el resultado por el tipo de datos hierarchyid .

SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

Este es el conjunto de resultados.

Converted Level  Level     Location         LocationType
---------------  --------  ---------------  ---------------
/1/              0x58      Europe           Continent
/1/1/            0x5AC0    France           Country
/1/1/1/          0x5AD6    Paris            City
/1/2/            0x5B40    Spain            Country
/1/2/1/          0x5B56    Madrid           City
/2/              0x68      South America    Continent
/2/1/            0x6AC0    Brazil           Country
/2/1/1/          0x6AD6    Brasilia         City
/2/1/2/          0x6ADA    Bahia            State
/2/1/2/1/        0x6ADAB0  Salvador         City
/3/              0x78      Antarctica       Continent
/3/1/            0x7AC0    McMurdo Station  City

La jerarquía tiene una estructura válida, aunque no sea coherente internamente. Bahia es el único estado. Aparece en la jerarquía como un homólogo de la ciudad Brasilia. Del mismo modo, McMurdo Station no tiene un país o región primario. Los usuarios deben decidir si este tipo de jerarquía es adecuado para su uso.

Agregue otra fila y seleccione los resultados.

INSERT BasicDemo
VALUES ('/1/3/1/', 'Kyoto', 'City'),
    ('/1/3/1/', 'London', 'City');

SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

Esto muestra más problemas posibles. Kyoto se puede insertar como el nivel /1/3/1/ aunque no hay ningún nivel primario /1/3/. Y tanto Londres como Kyoto tienen el mismo valor para el hierarchyid. Una vez más, los usuarios deben decidir si este tipo de jerarquía es adecuado para su uso y bloquear los valores que no se puedan usar.

Además, en esta tabla no se usó la parte superior de la jerarquía '/'. Se omitió porque no hay un padre común para todos los continentes. Puede añadir uno añadiendo el planeta entero.

INSERT BasicDemo
VALUES ('/', 'Earth', 'Planet');

Migrar de padre/hijo a hierarchyid

La mayoría de los árboles se representan mediante una relación padre/hijo. La manera más fácil de migrar de una estructura de elemento primario y secundario a una tabla que use hierarchyid consiste en usar una columna temporal o una tabla temporal para realizar el seguimiento del número de nodos en cada nivel de la jerarquía. Para ver un ejemplo de la migración de una tabla primaria/secundaria, consulte la lección 1 del Tutorial: Uso del tipo de datos hierarchyid.

Administración de un árbol mediante hierarchyid

Aunque una columna de hierarchyid no representa necesariamente un árbol, una aplicación puede exigir fácilmente que sí lo haga.

Cuando genere nuevos valores, realice uno de los siguientes pasos:
- Realice el seguimiento del último número secundario en la fila primaria.
- Calcule el último hijo. Si desea realizar eficazmente este proceso, deberá ejecutar un índice con prioridad a la amplitud.
Exija la singularidad creando un índice único en la columna, tal vez como parte de una clave de agrupación en clústeres. Para asegurarse de que se insertan valores únicos, realice uno de los siguientes pasos:
- Detecte errores y reintentos de infracción de clave única.
- Determine la singularidad de cada nuevo nodo secundario e insértelo como parte de una transacción serializable.

Ejemplo de uso de la detección de errores

En el ejemplo siguiente, el código de ejemplo calcula el nuevo valor secundario de EmployeeId y, después, detecta cualquier infracción de clave y la devuelve al marcador INS_EMP para volver a calcular el valor de EmployeeId para la nueva fila:

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

Ejemplo de uso de una transacción serializable

El índice Org_BreadthFirst garantiza que se use una búsqueda de rango al determinar @last_child. Además de otros casos de error que una aplicación pueda querer comprobar, una violación de clave duplicada después de la inserción indica un intento de añadir varios empleados con el mismo identificador y, por lo tanto, es necesario volver a calcular @last_child. En el código siguiente se calcula el nuevo valor de nodo dentro de una transacción serializable:

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;

    SELECT @last_child = EmployeeId.GetDescendant(LastChild, NULL)
    FROM Org_T2
    WHERE EmployeeId = @mgrid;

    UPDATE Org_T2
    SET LastChild = @last_child
    WHERE EmployeeId = @mgrid;

    INSERT Org_T2 (EmployeeId, EmployeeName)
    VALUES (@last_child, @EmpName);

    COMMIT;
END;

El código siguiente rellena la tabla con tres filas y devuelve los resultados:

INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES (HIERARCHYID::GetRoot(), 'David');
GO

EXECUTE AddEmp 0x, 'Sariya';
GO

EXECUTE AddEmp 0x58, 'Mary';
GO

SELECT * FROM Org_T2

Este es el conjunto de resultados.

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

Aplicar un árbol

Los ejemplos anteriores muestran cómo una aplicación puede asegurarse de que se mantenga un árbol. Para exigir un árbol mediante restricciones, se puede crear una columna calculada que defina el elemento primario de cada nodo con una restricción de clave externa respecto al identificador de clave principal.

CREATE TABLE Org_T3 (
    EmployeeId HIERARCHYID PRIMARY KEY,
    ParentId AS EmployeeId.GetAncestor(1) PERSISTED FOREIGN KEY REFERENCES Org_T3(EmployeeId),
    LastChild HIERARCHYID,
    EmployeeName NVARCHAR(50)
);
GO

Se prefiere este método de imponer una relación cuando el código en el que no se confía para mantener el árbol jerárquico tiene acceso directo de DML a la tabla. No obstante, este método puede reducir el rendimiento porque es necesario comprobar la restricción para cada operación DML.

Buscar ancestros mediante el CLR

Una operación común, en la que se implican dos nodos en una jerarquía, es buscar el antecesor común más bajo. Esta tarea se puede escribir en Transact-SQL o CLR porque el tipo de hierarchyid está disponible en ambos. Se recomienda CLR porque la ejecución es más rápida.

Use el siguiente código de CLR para hacer una lista de los antecesores y buscar el antecesor común más bajo:

using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server; // SqlFunction Attribute
using Microsoft.SqlServer.Types;  // SqlHierarchyId

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.IsDescendantOf(h2))
        {
            h1 = h1.GetAncestor(1);
        }

        return h1;
    }
}

Para usar los métodos ListAncestor y CommonAncestor en los siguientes ejemplos de Transact-SQL, genere la DLL y cree el ensamblado de HierarchyId_Operations en SQL Server ejecutando un código similar al ejemplo siguiente:

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

Enumerar antecesores

La creación de una lista de antecesores de un nodo es una operación común que sirve, por ejemplo, para mostrar la posición en una organización. Esto se puede realizar, por ejemplo, mediante una función con valores de tabla que use la clase HierarchyId_Operations definida anteriormente:

Uso de Transact-SQL:

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

Ejemplo de uso:

DECLARE @h AS 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
INNER JOIN ListAncestors(@h) AS A
    ON ED.OrgNode = A.Node
GO

Búsqueda del ancestro común más bajo

Use la clase HierarchyId_Operations definida anteriormente para crear la siguiente función de Transact-SQL a fin de buscar el antecesor común más bajo que implica dos nodos en una jerarquía:

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