Uso de los tipos de datos hierarchyid (Motor de base de datos)
El sistema proporciona el tipo de datos hierarchyid. Use hierarchyid como tipo de datos para crear tablas con una estructura jerárquica o para hacer referencia a la estructura jerárquica de datos de otra ubicación. Use funciones hierarchyid para consultar y trabajar con datos jerárquicos mediante Transact-SQL.
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 son aquellas en las que un elemento de los datos es el elemento primario de otro elemento. Los datos jerárquicos son comunes en las bases de datos. Entre otros, se incluyen los siguientes ejemplos:
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
El tipo hierarchyid es un nuevo elemento de SQL Server 2008 que facilita el almacenamiento y la consulta de datos jerárquicos. hierarchyid se optimiza para representar árboles, que son el tipo más común de datos jerárquicos.
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.
Limitaciones de hierarchyid
El tipo de datos hierarchyid tienen las siguientes limitaciones:
Una columna de tipo hierarchyid no representa de forma automática un árbol. Dependerá de la aplicación el generar y asignar valores hierarchyid de forma que la relación deseada entre las filas se refleje en los valores. Es posible que algunas aplicaciones ni siquiera deseen hacer que una columna de tipo hierarchyid represente un árbol. Quizás los valores son referencias a 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.
Estrategias de indización
Hay dos estrategias para indizar datos jerárquicos:
Con prioridad a la profundidad
En un índice con prioridad a la profundidad, las filas de un subárbol se almacenan las unas junto a las otras. Por ejemplo, todos los empleados al mando de un gerente se almacenan junto al registro de este último.
.gif "Los nodos se almacenan juntos.")
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.
.gif "Cada nivel de jerarquía se almacena junto.")
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 AdventureWorks ;
GO
CREATE TABLE Organization
(
EmployeeID hierarchyid,
OrgLevel as EmployeeID.GetLevel(),
EmployeeName nvarchar(50) NOT NULL
) ;
GO
En un índice con prioridad a la 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".
CREATE CLUSTERED INDEX Org_Breadth_First
ON Organization(OrgLevel,EmployeeID) ;
GO
CREATE UNIQUE INDEX Org_Depth_First
ON Organization(EmployeeID) ;
GO
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 a la profundidad, con prioridad a la 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.
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 AdventureWorks ;
GO
CREATE TABLE ParentChildOrg
(
EmployeeID int PRIMARY KEY,
ManagerId int REFERENCES ParentChildOrg(EmployeeID),
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 muy 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 sólo 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/secundario es mejor opción si la tabla de organización sólo se utiliza para ejecutar 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 muy importante, pero la mayoría de los movimientos se encuentran en un nivel bien determinado 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 alojada por un servicio. Los sitios contienen muchas páginas organizadas de forma jerárquica. Los sitios alojados 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. 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
Migrar de elemento primario/secundario a hierarchyid
La mayoría de los árboles se representan hoy en día utilizando una estructura de elemento primario/secundario. La manera más fácil de migrar de una estructura de elemento primario/secundario a una tabla que utilice hierarchyid es 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 sobre la migración de una tabla de elemento primario/secundario, consulte lección 1 de Tutorial: Uso del tipo de datos hierarchyid.
Transformaciones de consultas para hierarchyid
Para maximizar el rendimiento de las consultas de jerarquías, SQL Server realiza automáticamente tres transformaciones en ellas con hierarchyid. El resultado de estas transformaciones se puede ver en la salida del plan de presentación de las consultas transformadas.
IsDescendantOf se transforma en una búsqueda de intervalo
Habiendo establecido E como columna o variable, E.IsDescendantOf(c) se transforma en una búsqueda de intervalo. Esto reduce significativamente el costo de buscar descendientes. Si hay un índice con prioridad a la profundidad en E, esta transformación resulta útil, ya que todos los descendientes de E se ubican conjuntamente. Por ejemplo, el fragmento de código EmployeeId.IsDescendantOf(@value) se ejecuta como EmployeeId >= @Value AND EmployeeId <= @Value.DescendantLimit(). DescendantLimit es un método interno que determina el límite menos superior de todos los posibles descendientes de un nodo. Observe que @value no tiene que ser un parámetro. Puede ser una columna, quizás de una condición de combinación.
GetAncestor se transforma en un recorrido de intervalos y en un predicado residual
GetAncestor(n) proporciona el antecesor nº de un nodo. Esto es útil cuando se necesita la relación precisa (elemento primario, secundario, primario de segundo nivel, etc.) entre dos nodos en oposición al IsDescendantOf de carácter más general.
Por ejemplo, ejecute la consulta siguiente para buscar todos los empleados cuyo administrador directo sea @value:
SELECT * FROM Employees WHERE EmployeeId.GetAncestor(1) = @value
Esto se transforma en un examen del intervalo en busca de los descendientes de @value, con el predicado original definido como residuo. El código se transforma en lo siguiente:
SELECT * FROM Employees
WHERE
EmployeeId >= @Value AND EmployeeId <= @value.DescendantLimit()
AND EmployeeId.GetAncestor(1) = @value
El resultado es limitar el examen al subárbol de @value.
GetAncestor se transforma en una búsqueda del índice que utiliza el índice con prioridad a la amplitud
En la consulta anterior, si @value está en los niveles superiores del árbol, la optimización anterior no reducirá de manera significativa el número de filas examinadas. Cuando las preguntas sobre el antecesor nº son comunes, las aplicaciones deben crear un índice con prioridad a la amplitud, según se ha descrito anteriormente.
Cuando se encuentra un índice con prioridad a la amplitud, la consulta anterior se transforma en lo siguiente:
SELECT * FROM Employees
WHERE
EmployeeId >=@value AND EmployeeId <= @Value.DescendantLimit()
AND @value.GetLevel()+1 = EmployeeId.GetLevel()
La última línea (que contiene los métodos GetLevel) se transforma en una búsqueda del índice en el índice con prioridad a la amplitud. Si EmployeeId es la clave de agrupación en clústeres, entonces será la segunda columna del índice con prioridad a la amplitud, mientras que los dos predicados se convierten en una búsqueda del índice que especifica exactamente los n informes directos que se colocan conjuntamente de @value.
Las transformaciones GetAncestor no se limitan a las consultas de los elementos primarios directos. El argumento de GetAncestor puede ser cualquier variable o constante.