Datos jerárquicos (SQL Server)Hierarchical Data (SQL Server)

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

El tipo de datos hierarchyid integrado facilita el almacenamiento y la consulta de datos jerárquicos.The built-in hierarchyid data type makes it easier to store and query hierarchical data. hierarchyid se optimiza para representar los árboles, que son el tipo más común de datos jerárquicos.hierarchyid is optimized for representing trees, which are the most common type of hierarchical data.

Los datos jerárquicos se definen como un conjunto de elementos de datos que se relacionan entre sí mediante relaciones jerárquicas.Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. Las relaciones jerárquicas existen allí donde un elemento de los datos es el elemento primario de otro elemento.Hierarchical relationships exist where one item of data is the parent of another item. Entre los ejemplos de datos jerárquicos que se almacenan normalmente en las bases de datos se incluyen los siguientes:Examples of the hierarchical data that is commonly stored in databases include the following:

  • Una estructura organizativaAn organizational structure

  • Un sistema de archivosA file system

  • Un conjunto de tareas de un proyectoA set of tasks in a project

  • Una taxonomía de términos de idiomaA taxonomy of language terms

  • Un gráfico de vínculos entre páginas webA graph of links between Web pages

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 hierarchyid as a data type to create tables with a hierarchical structure, or to describe the hierarchical structure of data that is stored in another location. Use las funciones hierarchyid de Transact-SQLTransact-SQL para consultar y administrar los datos jerárquicos.Use the hierarchyid functions in Transact-SQLTransact-SQL to query and manage hierarchical data.

Propiedades principales de hierarchyidKey Properties of hierarchyid

Un valor del tipo de datos hierarchyid representa una posición en una jerarquía de árbol.A value of the hierarchyid data type represents a position in a tree hierarchy. Los valores de hierarchyid tienen las siguientes propiedades.Values for hierarchyid have the following properties:

  • Muy compactosExtremely compact

    El número medio de bits necesarios para representar un nodo en un árbol con n nodos depende del promedio de distribución ramificada secundarios (el promedio de elementos secundarios de un nodo).The average number of bits that are required to represent a node in a tree with n nodes depends on the average fanout (the average number of children of a node). Para distribuciones ramificadas pequeñas (0-7), el tamaño es aproximadamente 6*logAn bits, donde A es el promedio de distribución ramificada.For small fanouts, (0-7) the size is about 6*logAn bits, where A is the average fanout. Un nodo en una jerarquía organizativa de 100.000 personas con un promedio de nodos secundarios de 6 niveles supone aproximadamente 38 bits.A node in an organizational hierarchy of 100,000 people with an average fanout of 6 levels takes about 38 bits. Esto se redondea a 40 bits (o 5 bytes) para el almacenamiento.This is rounded up to 40 bits, or 5 bytes, for storage.

  • La comparación se realiza con prioridad a la profundidadComparison is in depth-first order

    Dados dos valores hierarchyid a y b, a<b significa que a viene antes que b en un corte transversal de prioridad a la profundidad del árbol.Given two hierarchyid values a and b, a<b means a comes before b in a depth-first traversal of the tree. 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.Indexes on hierarchyid data types are in depth-first order, and nodes close to each other in a depth-first traversal are stored near each other. Por ejemplo, los elementos secundarios de un registro se almacenan adyacentes a ese registro.For example, the children of a record are stored adjacent to that record.

  • Compatibilidad con inserciones y eliminaciones arbitrariasSupport for arbitrary insertions and deletions

    Con el método GetDescendant siempre es posible generar un miembro del mismo nivel a la derecha de cualquier nodo determinado, a la izquierda de cualquier nodo determinado, o entre dos miembros cualesquiera del mismo nivel.By using the GetDescendant method, it is always possible to generate a sibling to the right of any given node, to the left of any given node, or between any two siblings. Se mantiene la propiedad comparison cuando se inserta o elimina un número arbitrario de nodos de la jerarquía.The comparison property is maintained when an arbitrary number of nodes is inserted or deleted from the hierarchy. La mayoría de las inserciones y eliminaciones conservan la propiedad compactness.Most insertions and deletions preserve the compactness property. Sin embargo, las inserciones entre dos nodos generarán valores hierarchyid con una representación ligeramente menos compacta.However, insertions between two nodes will produce hierarchyid values with a slightly less compact representation.

Limitaciones de hierarchyidLimitations of hierarchyid

El tipo de datos hierarchyid tiene las siguientes limitaciones:The hierarchyid data type has the following limitations:

  • Una columna de tipo hierarchyid no representa automáticamente un árbol.A column of type hierarchyid does not automatically represent a tree. 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.It is up to the application to generate and assign hierarchyid values in such a way that the desired relationship between rows is reflected in the values. Algunas aplicaciones pueden tener una columna de tipo hierarchyid que indica la ubicación en una jerarquía definida en otra tabla.Some applications might have a column of type hierarchyid that indicates the location in a hierarchy defined in another table.

  • Depende de la aplicación el administrar la simultaneidad en la generación y asignación de valores hierarchyid .It is up to the application to manage concurrency in generating and assigning hierarchyid values. 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.There is no guarantee that hierarchyid values in a column are unique unless the application uses a unique key constraint or enforces uniqueness itself through its own logic.

  • Las relaciones jerárquicas representadas por valores hierarchyid no se aplican como una relación de clave externa.Hierarchical relationships represented by hierarchyid values are not enforced like a foreign key relationship. 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.It is possible and sometimes appropriate to have a hierarchical relationship where A has a child B, and then A is deleted leaving B with a relationship to a nonexistent record. Si este comportamiento no es aceptable, la aplicación debe consultar a los descendientes antes de eliminar los miembros primarios.If this behavior is unacceptable, the application must query for descendants before deleting parents.

Cuándo utilizar alternativas a hierarchyidWhen to Use Alternatives to hierarchyid

Dos alternativas a hierarchyid para representar los datos jerárquicos son:Two alternatives to hierarchyid for representing hierarchical data are:

  • Elemento primario/secundarioParent/Child

  • XMLXML

Normalmente,hierarchyid es mejor opción en comparación con estas alternativas.hierarchyid is generally superior to these alternatives. Sin embargo, hay situaciones concretas, que se detallan a continuación, donde es probable que las alternativas sean una mejor opción.However, there are specific situations detailed below where the alternatives are likely superior.

Elemento primario/secundarioParent/Child

Cuando se usa el planteamiento de elemento primario/secundario, cada fila contiene una referencia al elemento primario.When using the Parent/Child approach, each row contains a reference to the parent. 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:The following table defines a typical table used to contain the parent and the child rows in a Parent/Child relationship:

USE AdventureWorks2012 ;  
GO  
  
CREATE TABLE ParentChildOrg  
   (  
    BusinessEntityID int PRIMARY KEY,  
    ManagerId int REFERENCES ParentChildOrg(BusinessEntityID),  
    EmployeeName nvarchar(50)   
   ) ;  
GO  

Comparar el elemento primario/secundario y hierarchyid en operaciones comunesComparing Parent/Child and hierarchyid for Common Operations

  • Las consultas de subárboles son significativamente más rápidas con hierarchyid.Subtree queries are significantly faster with hierarchyid.

  • Las consultas directas de descendientes son ligeramente más lentas con hierarchyid.Direct descendant queries are slightly slower with hierarchyid.

  • Mover los nodos no hoja es más lento con hierarchyid.Moving non-leaf nodes is slower with hierarchyid.

  • Insertar nodos no hoja e insertar o mover nodos hoja es igual de complejo con hierarchyid.Inserting non-leaf nodes and inserting or moving leaf nodes has the same complexity with hierarchyid.

La estructura de elemento primario/secundario puede ser mejor opción cuando se dan las condiciones siguientes:Parent/Child might be superior when the following conditions exist:

  • El tamaño de la clave es crítico.The size of the key is critical. 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).For the same number of nodes, a hierarchyid value is equal to or larger than an integer-family (smallint, int, bigint) value. Esta es solo una de las razones para usar la estructura de elemento primario/secundario en casos poco comunes, ya que hierarchyid tiene una proximidad significativamente mejor de E/S y de complejidad de la CPU que las expresiones de tabla comunes necesarias cuando se usa una estructura de elemento primario/secundario.This is only a reason to use Parent/Child in rare cases, because hierarchyid has significantly better locality of I/O and CPU complexity than the common table expressions required when you are using a Parent/Child structure.

  • Las consultas raramente recorren todas las secciones de la jerarquía.Queries rarely query across sections of the hierarchy. Dicho de otro modo, las consultas normalmente se dirigen a un solo punto de la jerarquía.In other words, queries usually address only a single point in the hierarchy. En estos casos la ubicación conjunta no es importante.In these cases co-location is not important. 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.For example, Parent/Child is superior when the organization table is only used to process payroll for individual employees.

  • Los subárboles no hoja se mueven con frecuencia y el rendimiento es muy importante.Non-leaf subtrees move frequently and performance is very important. 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.In a parent/child representation changing the location of a row in a hierarchy affects a single row. Si se cambia la ubicación de una fila cuando se usa hierarchyid , ello afectará a n filas, donde n es el número de nodos de un subárbol que se están moviendo.Changing the location of a row in a hierarchyid usage affects n rows, where n is number of nodes in the sub-tree being moved.

    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.If the non-leaf subtrees move frequently and performance is important, but most of the moves are at a well-defined level of the hierarchy, consider splitting the higher and lower levels into two hierarchies. Esto convierte todos los movimientos en niveles de hoja de la jerarquía más alta.This makes all moves into leaf-levels of the higher hierarchy. Por ejemplo, considere la posibilidad de tener una jerarquía de sitios web hospedada por un servicio.For instance, consider a hierarchy of Web sites hosted by a service. Los sitios contienen muchas páginas organizadas de forma jerárquica.Sites contain many pages arranged in a hierarchical manner. Los sitios hospedados se pueden mover a otras ubicaciones en la jerarquía del sitio, pero las páginas subordinadas rara vez se reorganizan.Hosted sites might be moved to other locations in the site hierarchy, but the subordinate pages are rarely re-arranged. Esto se podría representar mediante:This could be represented via:

    CREATE TABLE HostedSites   
       (  
        SiteId hierarchyid, PageId hierarchyid  
       ) ;  
    GO  
    

XMLXML

Un documento XML es un árbol y, por lo tanto, una única instancia de tipo de datos XML puede representar una jerarquía completa.An XML document is a tree, and therefore a single XML data type instance can represent a complete hierarchy. In SQL ServerSQL Server when an XML dedex is created, hierarchyid values are used deternally to represent the position de the hierarchy.In SQL ServerSQL Server when an XML index is created, hierarchyid values are used internally to represent the position in the hierarchy.

Utilizar el tipo de datos XML puede ser mejor opción cuando se cumplen todas las condiciones siguientes:Using XML data type can be superior when all the following are true:

  • Siempre se almacena y se recupera la jerarquía completa.The complete hierarchy is always stored and retrieved.

  • La aplicación consume los datos en formato XML.The data is consumed in XML format by the application.

  • Las búsquedas del predicado están muy limitadas y no son vitales para el rendimiento.Predicate searches are extremely limited and not performance critical.

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:For example, if an application tracks multiple organizations, always stores and retrieves the complete organizational hierarchy, and does not query into a single organization, a table of the following form might make sense:

CREATE TABLE XMLOrg   
    (  
    Orgid int,  
    Orgdata xml  
    ) ;  
GO  

Estrategias de indización para los datos jerárquicosIndexing Strategies for Hierarchical Data

Hay dos estrategias para indizar datos jerárquicos:There are two strategies for indexing hierarchical data:

  • Con prioridad a la profundidadDepth-first

    En un índice con prioridad de profundidad, las filas de un subárbol se almacenan unas junto a otras.A depth-first index stores the rows in a subtree near each other. Por ejemplo, todos los empleados al mando de un gerente se almacenan junto al registro de este último.For example, all employees that report through a manager are stored near their managers' record.

    En un índice con prioridad de profundidad, todos los nodos del subárbol de un nodo se ubican conjuntamente.In a depth-first index, all nodes in the subtree of a node are co-located. 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".Depth-first indexes are therefore efficient for answering queries about subtrees, such as "Find all files in this folder and its subfolders".

  • Con prioridad a la amplitudBreadth-first

    Un índice con prioridad a la amplitud almacena juntas las filas de cada nivel de la jerarquía.A breadth-first stores the rows each level of the hierarchy together. Por ejemplo, se almacenan unos junto a otros los registros de empleados que notifican directamente al mismo gerente.For example, the records of employees who directly report to the same manager are stored near each other.

    En un índice con prioridad a la amplitud, todos los elementos secundarios directos de un nodo se ubican conjuntamente.In a breadth-first index all direct children of a node are co-located. 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".Breadth-first indexes are therefore efficient for answering queries about immediate children, such as "Find all employees who report directly to this manager".

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.Whether to have depth-first, breadth-first, or both, and which to make the clustering key (if any), depends on the relative importance of the above types of queries, and the relative importance of SELECT vs. DML operations. Para obtener un ejemplo detallado de las estrategias de indización, vea Tutorial: Uso del tipo de datos hierarchyid.For a detailed example of indexing strategies, see Tutorial: Using the hierarchyid Data Type.

Crear índicesCreating Indexes

El método GetLevel() se puede usar para crear una ordenación con prioridad a la amplitud.The GetLevel() method can be used to create a breadth first ordering. En el ejemplo siguiente se han creado los índices con prioridad a la amplitud y con prioridad a la profundidad:In the following example, both breadth-first and depth-first indexes are created:

USE AdventureWorks2012 ;   -- wmimof
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  

EjemplosExamples

Ejemplo sencilloSimple Example

El ejemplo siguiente es deliberadamente simplista para ayudarle a empezar.The following example is intentionally simplistic to help you get started. Cree primero una tabla que contenga algunos datos de geografía.First create a table to hold some geography data.

CREATE TABLE SimpleDemo  
(
    Level hierarchyid NOT NULL,  
    Location nvarchar(30) NOT NULL,  
    LocationType nvarchar(9) NULL
);

Ahora inserte datos para algunos continentes, países, estados y ciudades.Now insert data for some continents, countries, states, and cities.

INSERT SimpleDemo  
    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.Select the data, adding a column that converts the Level data into a text value that is easy to understand. Esta consulta también ordena el resultado por el tipo de datos hierarchyid .This query also orders the result by the hierarchyid data type.

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

El conjunto de resultados es el siguiente.Here is the result set.

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  

Observe que la jerarquía tiene una estructura válida, aunque no sea coherente internamente.Notice that the hierarchy has a valid structure, even though it is not internally consistent. Bahia es el único estado.Bahia is the only state. Aparece en la jerarquía como un homólogo de la ciudad Brasilia.It appears in the hierarchy as a peer of the city Brasilia. Del mismo modo, McMurdo Station no tiene un país primario.Similarly, McMurdo Station does not have a parent country. Los usuarios deben decidir si este tipo de jerarquía es adecuado para su uso.Users must decide if this type of hierarchy is appropriate for their use.

Agregue otra fila y seleccione los resultados.Add another row and select the results.

INSERT SimpleDemo  
    VALUES ('/1/3/1/', 'Kyoto', 'City'), ('/1/3/1/', 'London', 'City');  
SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;  

Esto muestra más problemas posibles.This demonstrates more possible problems. Kyoto se puede insertar como el nivel /1/3/1/ aunque no hay ningún nivel primario /1/3/.Kyoto can be inserted as level /1/3/1/ even though there is no parent level /1/3/. Y tanto London como Kyoto tienen el mismo valor de hierarchyid.And both London and Kyoto have the same value for the 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.Again, users must decide if this type of hierarchy is appropriate for their use, and block values that are invalid for their usage.

Además, en esta tabla no se usó la parte superior de la jerarquía '/'.Also, this table did not use the top of the hierarchy '/'. Se omitió porque no hay ningún elemento primario común de todos los continentes.It was omitted because there is no common parent of all the continents. Puede agregar uno si agrega todo el planeta.You can add one by adding the whole planet.

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

Tareas relacionadasRelated Tasks

Migrar de elemento primario/secundario a hierarchyidMigrating from Parent/Child to hierarchyid

La mayoría de los árboles se representan mediante elementos primario y secundario.Most trees are represented using Parent/Child. 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.The easiest way to migrate from a Parent/Child structure to a table using hierarchyid is to use a temporary column or a temporary table to keep track of the number of nodes at each level of the hierarchy. Para obtener un ejemplo sobre cómo migrar una tabla de elemento primario/secundario, vea la lección 1 de Tutorial: Uso del tipo de datos hierarchyid.For an example of migrating a Parent/Child table, see lesson 1 of Tutorial: Using the hierarchyid Data Type.

Administrar un árbol mediante hierarchyidManaging a Tree Using hierarchyid

Aunque una columna de hierarchyid no representa necesariamente un árbol, una aplicación puede exigir fácilmente que sí lo haga.Although a hierarchyid column does not necessarily represent a tree, an application can easily ensure that it does.

  • Cuando genere nuevos valores, realice una de las siguientes operaciones:When generating new values, do one of the following:

    • Realice el seguimiento del último número secundario en la fila primaria.Keep track of the last child number in the parent row.

    • Calcule el último elemento secundario.Compute the last child. Si desea realizar eficazmente este proceso, deberá ejecutar un índice con prioridad a la amplitud.Doing this efficiently requires a breadth-first index.

  • Exija la singularidad creando un índice único en la columna, tal vez como parte de una clave de agrupación en clústeres.Enforce uniqueness by creating a unique index on the column, perhaps as part of a clustering key. Para asegurarse de que se insertan valores únicos, realice una de las siguientes tareas:To ensure that unique values are inserted, do one of the following:

    • Detecte errores y reintentos de infracción de clave única.Detect unique key violation failures and retry.

    • Determine la singularidad de cada nuevo nodo secundario e insértelo como parte de una transacción serializable.Determine the uniqueness of each new child node, and insert it as part of a serializable transaction.

Ejemplo usando la detección de erroresExample Using Error Detection

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:In the following example, the sample code computes the new child EmployeeId value, and then detects any key violation and returns to INS_EMP marker to recompute the EmployeeId value for the new row:

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 usando una transacción serializableExample Using a Serializable Transaction

El índice Org_BreadthFirst garantiza que se use una búsqueda de rango al determinar @last_child.The Org_BreadthFirst index ensures that determining @last_child uses a range seek. Además de otros casos de error, es posible que una aplicación quiera 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, sea necesario volver a calcular @last_child.In addition to other error cases an application might want to check, a duplicate key violation after the insert indicates an attempt to add multiple employees with the same id, and therefore @last_child must be recomputed. En el código siguiente se calcula el nuevo valor de nodo dentro de una transacción serializable:The following code computes the new node value within a serializable transaction:

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:The following code populates the table with three rows and returns the results:

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.Here is the result set.

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

Exigir un árbolEnforcing a tree

Los ejemplos anteriores muestran cómo una aplicación puede asegurarse de que se mantenga un árbol.The above examples illustrate how an application can ensure that a tree is maintained. 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.To enforce a tree by using constraints, a computed column that defines the parent of each node can be created with a foreign key constraint back to the primary key id.

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.This method of enforcing a relationship is preferred when code that is not trusted to maintain the hierarchical tree has direct DML access to the table. No obstante, este método puede reducir el rendimiento porque es necesario comprobar la restricción para cada operación DML.However this method might reduce performance because the constraint must be checked on every DML operation.

Buscar antecesores mediante CLRFinding Ancestors by Using the 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.A common operation involving two nodes in a hierarchy is to find the lowest common ancestor. Esto se puede escribir en Transact-SQLTransact-SQL o CLR porque el tipo de hierarchyid está disponible en ambos.This can be written in either Transact-SQLTransact-SQL or CLR, because the hierarchyid type is available in both. Se recomienda CLR porque la ejecución es más rápida.CLR is recommended because performance will be faster.

Use el siguiente código de CLR para hacer una lista de los antecesores y buscar el antecesor común más bajo:Use the following CLR code to list ancestors and to find the lowest common ancestor:

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-SQLTransact-SQL , genere la DLL y cree el ensamblado de HierarchyId_Operations en SQL ServerSQL Server ejecutando un código similar al siguiente:To use the ListAncestor and CommonAncestor methods in the following Transact-SQLTransact-SQL examples, build the DLL and create the HierarchyId_Operations assembly in SQL ServerSQL Server by executing code similar to the following:

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

Enumerar antecesoresListing Ancestors

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.Creating a list of ancestors of a node is a common operation, for instance to show position in an organization. Esto se puede realizar, por ejemplo, mediante una función con valores de tabla que use la clase HierarchyId_Operations definida anteriormente:One way of doing this is by using a table-valued-function using the HierarchyId_Operations class defined above:

Usar Transact-SQLTransact-SQL:Using Transact-SQLTransact-SQL:

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

Ejemplo de uso:Example of usage:

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  

Buscar el antecesor común más bajoFinding the Lowest Common Ancestor

Use la clase HierarchyId_Operations definida anteriormente para crear la siguiente función de Transact-SQLTransact-SQL a fin de buscar el antecesor común más bajo que implica dos nodos en una jerarquía:Using the HierarchyId_Operations class defined above, create the following Transact-SQLTransact-SQL function to find the lowest common ancestor involving two nodes in a hierarchy:

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

Ejemplo de uso:Example of usage:

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/The resultant node is /1/1/

Mover los subárbolesMoving Subtrees

Otra operación común es mover subárboles.Another common operation is moving subtrees. El procedimiento siguiente toma el subárbol de @oldMgr y lo convierte (incluido @oldMgr) en un subárbol de @newMgr.The procedure below takes the subtree of @oldMgr and makes it (including @oldMgr) a subtree of @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  

Consulte tambiénSee Also

Referencia de los métodos del tipo de datos hierarchyid hierarchyid Data Type Method Reference
Tutorial: Uso del tipo de datos hierarchyid Tutorial: Using the hierarchyid Data Type
hierarchyid (Transact-SQL)hierarchyid (Transact-SQL)