Datos jerárquicos (SQL Server)
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:
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.
En este tema
Propiedades principales de hierarchyid
Limitaciones de hierarchyid
Cuándo utilizar alternativas a hierarchyid
Estrategias de indización para los datos jerárquicos
Tareas relacionadas
Migrar de elemento primario/secundario a hierarchyid
Administrar un árbol mediante hierarchyid
Exigir un árbol
Buscar antecesores mediante CLR
Enumerar antecesores
Buscar el antecesor común más bajo
Mover los subárboles
Propiedades principales de hierarchyid
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.
Muy compactos
El número medio de bits que se exigen para representar un nodo en un árbol con n nodos depende del promedio de nodos secundarios (el número medio de elementos secundarios de un nodo). Para multiplicadores de salida pequeños (0-7), el tamaño es aproximadamente 6*logAn bits, donde A es el promedio de nodos secundarios. Un nodo en una jerarquía organizativa de 100.000 personas con un promedio de nodos secundarios de 6 niveles supone aproximadamente 38 bits. Esto se redondea a 40 bits (o 5 bytes) para el almacenamiento.
La comparación se realiza con prioridad a la profundidad
Dados dos valores hierarchyid a y b, a<b quiere decir que a viene antes que b en un corte transversal de prioridad a la 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 elementos secundarios de un registro se almacenan junto a ese registro.
Compatibilidad con inserciones y eliminaciones arbitrarias
Si utiliza el método GetDescendant, siempre es posible generar un elemento relacionado a la derecha de cualquier nodo dado, a la izquierda de cualquier nodo dado o entre dos elementos cualesquiera relacionados. Se mantiene la propiedad comparison cuando se inserta o elimina un número arbitrario de nodos de la jerarquía. La mayoría de las inserciones y eliminaciones conservan la propiedad compactness. Sin embargo, las inserciones entre dos nodos generarán valores hierarchyid con una representación ligeramente menos compacta.
[ARRIBA]
Limitaciones de hierarchyid
El tipo de datos hierarchyid tiene las siguientes limitaciones:
Una columna de tipo hierarchyid no representa de forma automática un árbol. Dependerá 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.
Depende de la aplicación el administrar la simultaneidad en la generación y asignación de 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.
[ARRIBA]
Cuándo utilizar alternativas a hierarchyid
Dos alternativas a hierarchyid para representar los datos jerárquicos son:
Elemento primario/secundario
XML
Normalmente, hierarchyid es mejor opción en comparación con estas alternativas. Sin embargo, hay situaciones concretas, que se detallan a continuación, donde es probable que las alternativas sean una mejor opción.
Elemento primario/secundario
Cuando se usa el planteamiento de elemento primario/secundario, cada fila contiene una referencia al elemento primario. La tabla siguiente define una tabla típica que se usa para contener las filas del elemento primario y el secundario en una relación entre elemento primario y secundario:
USE AdventureWorks2012 ;
GO
CREATE TABLE ParentChildOrg
(
BusinessEntityID int PRIMARY KEY,
ManagerId int REFERENCES ParentChildOrg(BusinessEntityID),
EmployeeName nvarchar(50)
) ;
GO
Comparar el planteamiento de elemento primario/secundario y hierarchyid en 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 no hoja es más lento con hierarchyid.
Insertar nodos no hoja e insertar o mover nodos hoja es igual de complejo con hierarchyid.
La estructura de elemento primario/secundario puede ser mejor opción cuando se dan las condiciones siguientes:
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). Ésta es solo una de las razones para utilizar la estructura de elemento primario/secundario en casos poco comunes porque hierarchyid tiene una proximidad significativamente mejor de E/S y de complejidad de la CPU que las expresiones de tabla comunes necesarias cuando se utiliza una estructura de elemento primario/secundario.
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 ubicación conjunta no es importante. Por ejemplo, la estructura de elemento primario y secundario es la mejor opción cuando la tabla de organización solo se usa para procesar la nómina de empleados individuales.
Los subárboles no hoja se mueven con frecuencia y el rendimiento es muy importante. En una representación de elemento primario/secundario, el cambio de 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 utiliza hierarchyid, ello 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 encuentran en un nivel bien definido de la jerarquía, tenga en cuenta la posibilidad de dividir los niveles más altos y más bajos 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
[ARRIBA]
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. Cuando se crea un índice XML en SQL Server, se usan internamente los valores hierarchyid 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 del predicado están muy limitadas y no son vitales 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
[ARRIBA]
Estrategias de indización para los datos jerárquicos
Hay dos estrategias para indizar datos jerárquicos:
Con prioridad a la profundidad
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 con prioridad de profundidad, todos los nodos del subárbol de un nodo se ubican conjuntamente. Por lo tanto, los índices con prioridad a la profundidad son eficaces para responder a las consultas sobre subárboles, como "Buscar todos los archivos en esta carpeta y en sus subcarpetas".
Con prioridad a la amplitud
Un índice con prioridad a la 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 con prioridad a la amplitud, todos los elementos secundarios directos de un nodo se ubican conjuntamente. Por lo tanto, los índices con prioridad a la amplitud son eficaces para responder a las consultas sobre elementos secundarios inmediatos, como "Buscar todos los empleados que informan directamente a este gerente".
Saber si es mejor tener un índice con prioridad de profundidad, con prioridad de amplitud, o ambos, y cuál de estos se debe establecer como clave de agrupación en clústeres (cuando proceda), depende de la importancia relativa de los tipos de consultas anteriores y de la importancia relativa de las operaciones SELECT frente a las de DML. Para obtener un ejemplo detallado de las estrategias de indización, consulte Tutorial: Uso del tipo de datos hierarchyid.
[ARRIBA]
Ejemplos
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 AdventureWorks2012 ;
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
[ARRIBA]
Tareas relacionadas
Migrar de elemento primario/secundario a hierarchyid
La mayoría de los árboles se representan mediante elementos primario y secundario. La manera más fácil de migrar de una estructura de elemento primario y secundario a una tabla que use hierarchyid consiste en utilizar 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 sobre la migración de una tabla de elemento primario/secundario, consulte lección 1 de Tutorial: Uso del tipo de datos hierarchyid.
[ARRIBA]
Administrar 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 una de las siguientes operaciones:
Realice el seguimiento del último número secundario en la fila primaria.
Calcule el último elemento secundario. 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 una de las siguientes tareas:
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.
[ARRIBA]
Ejemplo usando la detección de errores
En el ejemplo siguiente, el código de ejemplo calcula el nuevo valor secundario de EmployeeId y, a continuación, detecta cualquier infracción de clave y vuelve 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 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
[ARRIBA]
Ejemplo usando 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, es posible que una aplicación desee comprobar una infracción de clave duplicada después de que la inserción indique un intento de agregar varios empleados con el mismo identificador y, por lo tanto, es necesario volver a calcular @last_child. El código siguiente usa una transacción serializable y un índice con prioridad a la amplitud para calcular el nuevo valor de nodo:
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 ;
El código siguiente rellena la tabla con tres filas y devuelve los resultados:
INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2
El conjunto de resultados es el siguiente.
EmployeeId LastChild EmployeeName
---------- --------- ------------
0x 0x58 David
0x58 0x5AC0 Sariya
0x5AC0 NULL Mary
[ARRIBA]
Exigir 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
REFERENCES Org_T3(EmployeeId),
LastChild hierarchyid,
EmployeeName nvarchar(50)
)
GO
Se prefiere este método que exige una relación cuando el código que no es de confianza para mantener el árbol jerárquico tiene acceso DML directo a la tabla. No obstante, este método puede reducir el rendimiento porque es necesario comprobar la restricción para cada operación DML.
[ARRIBA]
Buscar antecesores mediante 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. Esto 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;
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;
}
}
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 siguiente:
CREATE ASSEMBLY HierarchyId_Operations
FROM '<path to DLL>\ListAncestors.dll'
GO
[ARRIBA]
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:
Usar 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 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
[ARRIBA]
Buscar el antecesor 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
Ejemplo de uso:
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) ;
El nodo resultante es /1/1/
[ARRIBA]
Mover los subárboles
Otra operación común es mover subárboles. El procedimiento siguiente toma el subárbol de @oldMgr y lo convierte (incluido @oldMgr) en un subárbol de @newMgr.
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 OrgNode.IsDescendantOf(@nold) = 1 ;
COMMIT TRANSACTION
END ;
GO
[ARRIBA]